Oct 15 2019 02:28 AM
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.
Oct 15 2019 02:42 AM
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.
Oct 15 2019 02:55 AM
Oct 15 2019 03:32 AM
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.
Feb 18 2021 05:17 AM
@Sergei Baklan thanks for this man. this really helped me on appending all of the data as one table I'm new to queries, VBA and pivot so I'm in a newbie stage.
May 10 2022 10:21 AM
Its an elegant solution as it becomes 'sheet-name' agnostic.
I was wondering if it is possible to store the sheetname to import as a query parameter and then supply that where it is required to insert the name of the sheet?
Appreciate
May 10 2022 10:29 AM
You may name the cell in the grid which has sheet name, query it and use result in other queries, like
getSheetName = Excel.CurrentWorkbook(){[Name="sheetname"]}[Content]
where "sheetname" is the name of the cell.