Home

Power Query Excel data from Variable / Dynamic Worksheet...

%3CLINGO-SUB%20id%3D%22lingo-sub-915044%22%20slang%3D%22en-US%22%3EPower%20Query%20Excel%20data%20from%20Variable%20%2F%20Dynamic%20Worksheet...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-915044%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20power%20query%20to%20pull%20data%20from%20a%20weekly%20file%20via%26nbsp%3B%20filepath%20and%20name%20in%20cell%20reference%2C%20which%20is%20working%20perfectly.%20However%20when%20I%20change%20week%20number%2C%20worksheet%20name%20for%20next%20week%20file%20changes%20as%20well%20and%20producing%20an%20error.%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20I%20can%20change%20M.Code%20to%20take%20data%20from%20first%20sheet%20in%20workbook%20as%20there%20is%20only%20one%20sheet%20in%20every%20week%20file%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20ideas.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-915044%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-915062%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Excel%20data%20from%20Variable%20%2F%20Dynamic%20Worksheet...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-915062%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F426283%22%20target%3D%22_blank%22%3E%40Kashibaba%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20change%20in%20Navigation%20step%20the%20code%20which%20is%20usually%20looks%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESheet1_Sheet%20%3D%20Source%7B%5BItem%3D%22Sheet1%22%2CKind%3D%22Sheet%22%5D%7D%5BData%5D%2C%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eon%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESheet1_Sheet%20%3D%20Source%7B0%7D%5BData%5D%2C%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EOther%20words%2C%20use%20relative%20reference%20instead%20of%20absolute%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-915070%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Excel%20data%20from%20Variable%20%2F%20Dynamic%20Worksheet...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-915070%22%20slang%3D%22en-US%22%3EThank%20you%20Sergei.%3CBR%20%2F%3EI%20removed%201st%20column%20(Name)%20showing%20sheet%20name%20added%20by%20query%20itself%20and%20refresh%20query.%20It%20did%20work%20and%20now%20sheet%20Name%20column%20refreshing%20as%20well%20without%20extra%20hard%20coding.%20Also%20I%20don't%20have%20Navigation%20step%20in%20my%20applied%20step%20may%20be%20because%20of%20setting%20file%20Path%20for%20the%20source.%3CBR%20%2F%3EThanks%20again%20for%20a%20quick%20suggestion%20which%20may%20be%20helpful%20in%20some%20other%20but%20similar%20cases.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-915132%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Excel%20data%20from%20Variable%20%2F%20Dynamic%20Worksheet...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-915132%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F426283%22%20target%3D%22_blank%22%3E%40Kashibaba%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20that's%20also%20the%20way%20if%20you%20have%20only%20one%20sheet.%20The%20idea%20is%20when%20connecting%20to%20Excel%20file%20connector%20takes%20sheets%20one%20by%20one%20in%20sequential%20order.%20First%20record%20is%20always%20for%20the%20first%20sheet.%20Instead%20of%20taking%20it%20by%20record%20metadata%20you%20may%20take%20just%20first%20record.%20In%20your%20case%20both%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.Workbook(File.Contents(%22C%3A%5CTest%5CBook1.xlsx%22)%2C%20null%2C%20true)%2C%0A%20%20%20%20%23%22Removed%20Other%20Columns%22%20%3D%20Table.SelectColumns(Source%2C%7B%22Data%22%7D)%2C%0A%20%20%20%20%23%22Expanded%20Data%22%20%3D%20Table.ExpandTableColumn(%23%22Removed%20Other%20Columns%22%2C%20%22Data%22%2C...%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.Workbook(File.Contents(%22C%3A%5CTest%5CBook1.xlsx%22)%2C%20null%2C%20true)%7B0%7D%5BData%5D%0Ain%0A%20%20%20%20Source%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eshall%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Kashibaba
Occasional Contributor

Hi All, 

I'm using power query to pull data from a weekly file via  filepath and name in cell reference, which is working perfectly. However when I change week number, worksheet name for next week file changes as well and producing an error. 

How I can change M.Code to take data from first sheet in workbook as there is only one sheet in every week file? 

Thanks for ideas.

3 Replies

@Kashibaba 

You may change in Navigation step the code which is usually looks like

Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

on

Sheet1_Sheet = Source{0}[Data],

Other words, use relative reference instead of absolute one.

Thank you Sergei.
I removed 1st column (Name) showing sheet name added by query itself and refresh query. It did work and now sheet Name column refreshing as well without extra hard coding. Also I don't have Navigation step in my applied step may be because of setting file Path for the source.
Thanks again for a quick suggestion which may be helpful in some other but similar cases.

@Kashibaba 

Yes, that's also the way if you have only one sheet. The idea is when connecting to Excel file connector takes sheets one by one in sequential order. First record is always for the first sheet. Instead of taking it by record metadata you may take just first record. In your case both

let
    Source = Excel.Workbook(File.Contents("C:\Test\Book1.xlsx"), null, true),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data",... 

and

let
    Source = Excel.Workbook(File.Contents("C:\Test\Book1.xlsx"), null, true){0}[Data]
in
    Source

shall work.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies