Forum Discussion
Expand data table only for 1st sheet without specifying sheet name
Hi Anonymous ,
You can directly start with blank query.
You can use this query and modify as per your folder name-
let
Source = Folder.Files("C:\\Your\\Folder\\Path") // Change this to your folder path
ExcelFiles = Table.SelectRows(Source, each Text.EndsWith([Extension], ".xlsx") or Text.EndsWith([Extension], ".xls")),
AddFirstSheet = Table.AddColumn(ExcelFiles, "FirstSheet", each try Excel.Workbook([Content], null, true){0}[Data] otherwise null),
RemoveNulls = Table.SelectRows(AddFirstSheet, each [FirstSheet] <> null),
ExpandedData = Table.ExpandTableColumn(RemoveNulls, "FirstSheet", Table.ColumnNames(RemoveNulls{0}[FirstSheet])),
FinalTable = Table.SelectColumns(ExpandedData, {"Name"} & Table.ColumnNames(RemoveNulls{0}[FirstSheet]))
in
FinalTable
This will get you one table with the first sheet from every file
If column names are inconsistent across sheets, consider normalizing them before combining.
Hope this helps!
Hi Anonymous To acheive this, follow the steps:
Considering you just loaded a single file in power query and the changes each week with different first sheet name. To dynamically select the first sheet only, regardless of name:
after loading file to power query > go to home > open advance editor and change the following line:
From:
after changes, the editor look like this:
But if have folder connector and combining multiple files and only want to consider the first sheet, then first expand files using any sheet you like. After expansion, click on the Transform Sample File > open advance editor and changes any thing inside curely brackets of souce line with 0. See images:
Replace 1 with 0. Where 1 means second sheet and 0 means first.
Here is the desire transformation:
Hope this helps!!
BR,
Shafiz
Thanks but my data is not coming by Transform file from query, it is from location as my code.
So it doesn't navigate pargicular Sheet.