Dynamic Sheet Name - Power Query

E

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.
Sort by date Sort by votes
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
GraH said:
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.
Click to expand...
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
egoosen3 said:
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
Click to expand...
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
GraH said:
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.
Click to expand...
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
GraH said:
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.
Click to expand...
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
You must log in or register to reply here.

Similar threads

D
  • Question
Power Query refresh taking HOURS after adding custom column
Replies
3
Views
546
Matt Allington
M
G
  • Question
Power Query - Change Type Dynamically
2
Replies
12
Views
611
gelu
G
earthworm
  • Question
Exporting Data from Power Query into Multiple Sheet and merging steps into one.
Replies
3
Views
477
Matt Allington
M
M
  • Solved
Power Query not refreshing the source files in folder
Replies
5
Views
787
smozgur
smozgur
T
  • Question
Help converting Power Query that imports data to VBA
Replies
0
Views
379
The Shy Retirer
T

Forum statistics

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

Share this page

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