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]

Try to update [Item] = MySheet to [Name] = MySheet in the step Query_sheet.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Power Query - Feeding dynamic sheet name to Power query
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[jdellasala]

I'm on the Insider edition of 365 where this has been fixed, however in earlier versions, when you went right from a new Query to a table on a new sheet, it would always come in as Sheet#, whereas if you loaded it as a Connection Only, and then loaded it as a Table on a new Worksheet, the Worksheet would use the name of the query.
Not sure if that would answer your problem, but thought I'd mention it anyway!

 

[egoosen3]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Power Query - Feeding dynamic sheet name to Power query
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Apologies, will do