Dynamic Sheet Name - Power Query

E

egoosen3

New Member
Joined
Mar 11, 2012
Messages
18
Hi, I import date from another excel file via Power Query. The sheet names change monthly. I want to feed the required sheet name to the Power Query. Cant get it working.
used the script below:
Power Query: 
let
    custom_sheet = Excel.CurrentWorkbook(){[Name="mySheet"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents("C:\Users\eugeneg\Ora Fund Managers\SAIF Tech - Documents\General\TBI PrefCo\Model\MMKT Files\NINETY ONE CORPORATE MONEY MARKET FUND A CLASS.xls"), null, true),
    query_sheet = Source{[Item=custom_sheet,Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(query_sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",18),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column1", "Column4"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",null,"",Replacer.ReplaceValue,{"Column4"})
in
    #"Replaced Value"
the sheet name is defined in range name mySheet = December 2022 (the sheetname is in date format - 1/12/2022 - MMMM YYYY )
Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Item=01/10/2022 00:00:00
Kind=Sheet
Table=


I manually changed the sheet name to text and the dynamic name to the same and get the same error
 
Last edited by a moderator:
Sort by date Sort by votes
Update your code as followed.
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="MySheet"]}[Content],
    custom_sheet = Table.TransformColumns(Source, {{"Column1", each Date.MonthName(_) & " " & Text.From(Date.Year(_)), type text}})[Column1]{0}
in
   custom_sheet
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
GraH said:
Update your code as followed.
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="MySheet"]}[Content],
    custom_sheet = Table.TransformColumns(Source, {{"Column1", each Date.MonthName(_) & " " & Text.From(Date.Year(_)), type text}})[Column1]{0}
in
   custom_sheet
Click to expand...
How do I deal with the file path and name that was defined as Source above?
 
Upvote 0
You can rename to Source1, Source2... or say replace Source1 by thisWB or so.
 
Upvote 0
GraH said:
You can rename to Source1, Source2... or say replace Source1 by thisWB or so.
Click to expand...
I adapted it:
let
Source = Excel.CurrentWorkbook(){[Name="[COLOR=rgb(247, 218, 100)]mySheet[/COLOR]"]}[Content],
custom_sheet = Table.TransformColumns(Source, {{"Column1", each Date.MonthName(_) & " " & Text.From(Date.Year(_)), type text}})[Column1]{0},
Source1 = Excel.Workbook(File.Contents("C:\Users\eugeneg\Ora Fund Managers\SAIF Tech - Documents\General\TBI PrefCo\Model\MMKT Files\NINETY ONE CORPORATE MONEY MARKET FUND A CLASS.xls"), null, true),
query_sheet = Source1{[Item=custom_sheet,Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(query_sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",18),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column1", "Column4"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",null,"",Replacer.ReplaceValue,{"Column4"})
in
#"Replaced Value"

error

Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Item=December 2022
Kind=Sheet
Table=
 
Upvote 0
Can you share what's in the UI at this point of the script?
Power Query: 
query_sheet = Excel.CurrentWorkbook(){[Name="mySheet"]}[Content],
and here
Power Query: 
Source =  [B]Source1[/B]{[Item=custom_sheet,Kind="Sheet"]}[Data]}
 
Upvote 0
GraH said:
Can you share what's in the UI at this point of the script?
Power Query: 
query_sheet = Excel.CurrentWorkbook(){[Name="[COLOR=rgb(247, 218, 100)]mySheet[/COLOR]"]}[Content],
and here
Power Query: 
Source = Excel.CurrentWorkbook(){[Name="[COLOR=rgb(247, 218, 100)]mySheet[/COLOR]"]}[Content]
Click to expand...
What is UI?
 
Upvote 0
User Interface.
Like this.
1670929818864.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Power Query refresh taking HOURS after adding custom column
Replies
3
Views
546
Matt Allington
M
G
  • Question
Power Query - Change Type Dynamically
2
Replies
12
Views
611
gelu
G
earthworm
  • Question
Exporting Data from Power Query into Multiple Sheet and merging steps into one.
Replies
3
Views
477
Matt Allington
M
M
  • Solved
Power Query not refreshing the source files in folder
Replies
5
Views
787
smozgur
smozgur
T
  • Question
Help converting Power Query that imports data to VBA
Replies
0
Views
379
The Shy Retirer
T

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top