Dynamic Sheet Name - Power Query

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:
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.
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
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
You can rename to Source1, Source2... or say replace Source1 by thisWB or so.
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
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]
What is UI?
 
Upvote 0
User Interface.
Like this.
1670929818864.png
 
Upvote 0

Forum statistics

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

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