Dynamic Sheet Name - Power Query

[egoosen3]

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:

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

 

[GraH]

Update your code as followed.

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

 

[egoosen3]

Update your code as followed.

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?

 

[GraH]

You can rename to Source1, Source2... or say replace Source1 by thisWB or so.

 

[egoosen3]

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=

 

[GraH]

Can you share what's in the UI at this point of the script?

query_sheet = Excel.CurrentWorkbook(){[Name="mySheet"]}[Content],

and here

Source =  [B]Source1[/B]{[Item=custom_sheet,Kind="Sheet"]}[Data]}

 

[egoosen3]

Can you share what's in the UI at this point of the script?

query_sheet = Excel.CurrentWorkbook(){[Name="[COLOR=rgb(247, 218, 100)]mySheet[/COLOR]"]}[Content],

and here

Source = Excel.CurrentWorkbook(){[Name="[COLOR=rgb(247, 218, 100)]mySheet[/COLOR]"]}[Content]

What is UI?

 

[GraH]

User Interface.
Like this.

 

[egoosen3]

User Interface.
Like this.
View attachment 80768

 

[egoosen3]

View attachment 80771

 

[egoosen3]

View attachment 80772