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 ,
You can follow these steps:
After getting data from your SharePoint by:
Get Data → SharePoint Folder
Go to Home > Get Data > SharePoint Folder
Paste the root SharePoint site URL (not the folder URL)
1. Filter to your target folder
In Power Query, filter down to the files you want. Make sure you're only keeping .xlsx file
Keep these columns -> Content, Name, Folder Path.
2. Add a custom column(FirstSheet) and paste this M code
= let
Source = Excel.Workbook([Content], null, true),
SheetsOnly = Table.SelectRows(Source, each [Kind] = "Sheet"),
FirstSheet = if Table.RowCount(SheetsOnly) > 0 then SheetsOnly{0}[Data] else null
in
FirstSheet
3.Remove [Content] column, and expand the new FirstSheet column:
Click the little expand icon next to FirstSheet column --> Expand all columns in the data.
Hope this helps!
If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
Hi any reason it should start from Home - Get data rather than writing blank query?
Can you rewrite the suggestion with my code?
I need all the excel file data expansion not locating particular since it is opening data only 1st place sheet but I need to open all of 1st sheets from all the files.