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:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
Maybe you should try to record a Macro from the beginning of creating a Power Query.
I think you wil find something interesting.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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=
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
1670921521255.png


1670921467516.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