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]

How many sheets are in the source?
You might not need to reference the name. Filter on sheet type, select data column, remove other columns then use PreviousStepName [Data]{0}.
If there is only 1 Sheet, this should work.

 

[HongRu]

Maybe you should try to record a Macro from the beginning of creating a Power Query.
I think you wil find something interesting.

 

[egoosen3]

How many sheets are in the source?
You might not need to reference the name. Filter on sheet type, select data column, remove other columns then use PreviousStepName [Data]{0}.
If there is only 1 Sheet, this should work.

There are multiple sheets, I know there is a way to capture the sheet name in the current sheet, but I am doing something wrong some where

 

[GraH]

There are multiple sheets, I know there is a way to capture the sheet name in the current sheet, but I am doing something wrong some where

Make a query that reads the table from current workbook. Drill Down on the value that is your sheetname.
Refer to this Query as a parameter in the string you construct to get to that sheet of the other workbook.
I do think that's what you are trying to do.
Looking at the construct for custom_sheet you stop at creating a list in your first Query step, not a value. You may try with adding {0} at the end.

 

[egoosen3]

Make a query that reads the table from current workbook. Drill Down on the value that is your sheetname.
Refer to this Query as a parameter in the string you construct to get to that sheet of the other workbook.
I do think that's what you are trying to do.
Looking at the construct for custom_sheet you stop at creating a list in your first Query step, not a value. You may try with adding {0} at the end.

Thanks, I am completely out of my depth with M code, got the script on the web. So I added the {0} at the end and getting:
Expression.Error: The key didn't match any rows in the table.
Details:
Key=[Record]
Table=

 

[GraH]

Can you share that table please, in which you store that variable. I'm without excel for the moment, but will have a look later. Reading M code on a phone is no fun.
I'm overlooking something obvious I guess.

 

[egoosen3]

Can you share that table please, in which you store that variable. I'm without excel for the moment, but will have a look later. Reading M code on a phone is no fun.
I'm overlooking something obvious I guess.

So the sheet name is captured in a named Excel range , a single named cell called mySheet. Thus not a table.

 

[GraH]

Sorry, I'm on a PC without XL1BB

A bit puzzled by your sheetname, is it with the date in or just the month and the year? Anyway, you can adapt accordingly.

 

[egoosen3]

Sorry, I'm on a PC without XL1BB

A bit puzzled by your sheetname, is it with the date in or just the month and the year? Anyway, you can adapt accordingly.
View attachment 80759

View attachment 80758

The sheetname = December 2022 = MMMMYYYY format (it is system generated)