Forum Discussion
Table from multiple Excel Sheets
Here's a starting point:
let Source = Folder.Files("C:\Users\xxx\Downloads"), #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Mock")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.SelectColumns(Excel.Workbook([Content]),{"Name","Data"})), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data"}, {"Sheet", "Data"}), #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Name", "Sheet", "Data"}), #"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Location", each [Data]{1}[Column2]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Date", each [Data]{0}[Column2]), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Type", each [Data]{2}[Column2]), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Checked by", each [Data]{Table.RowCount([Data])-1}[Column2]) in #"Added Custom4"This yields
Now you will have to decide if you want to expand this with the actual numbers or if those should be put into a separate table and unpivoted.
For example:
let Source = Folder.Files("C:\Users\xxx\Downloads"), #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Mock")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.SelectColumns(Excel.Workbook([Content]),{"Name","Data"})), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data"}, {"Sheet", "Data"}), #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Name", "Sheet", "Data"}), #"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Location", each [Data]{1}[Column2]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Date", each [Data]{0}[Column2]), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Type", each [Data]{2}[Column2]), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Checked by", each [Data]{Table.RowCount([Data])-1}[Column2]), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Cleaned", each Table.UnpivotOtherColumns(Table.PromoteHeaders(Table.SelectRows(Table.RemoveLastN(Table.Skip([Data],4),2), each ([Column1] <> null)), [PromoteAllScalars=true]), {"Evaluation", "Note"}, "Attribute", "Value")), #"Removed Other Columns2" = Table.SelectColumns(#"Added Custom5",{"Name", "Sheet", "Location", "Date", "Type", "Checked by", "Cleaned"}) in #"Removed Other Columns2"which could then expand into
That is a pretty standard double enumeration pattern.
In your Power Query, connect to the folder location where you keep the Excel files (do not "connect to File"!). Then add a filter that singles out the excel files you want to ingest. Now for each file write a function that extracts a particular sheet. Then combine the results.
Then rinse and repeat with the other sheets.
Hello
Appreciate the response, slightly late follow-up, I'm not 100% sure if I know what you mean.
If you could link a guide or even the page to reference in the Introduction Handbook I would appreciate it, if possible.
Thank you.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Alright, I have uploaded a mock sample of my data to a Google Drive Folder
It contains 3 Excel files, representing sample data from 3 locations (Termed Location 1, Location 2 and Location 3), where Location 1 and 2 have 2 sets of data recorded at different dates by week.
From these 5 Excel sheets I wish to create a table in PowerBI a bit like this:Location Date Checked By: Location 1 [Dropdown] [Name] Location 2 [Dropdown] [Name] Location 3 [Dropdown] [Name] Where each row drills through to the specific data for each Excel sheet (each location at chosen date) as follows:
Evaluation Note Point1 Point2 Point3 Point4 10+1 20+1 30+1 40+1 Max (I understand how to use the Drillthrough function itself but not how to organize my data in PowerQuery)
Hopefully that is understood, apologies otherwise.
Thank you.Here's a starting point:
let Source = Folder.Files("C:\Users\xxx\Downloads"), #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Mock")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.SelectColumns(Excel.Workbook([Content]),{"Name","Data"})), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data"}, {"Sheet", "Data"}), #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Name", "Sheet", "Data"}), #"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Location", each [Data]{1}[Column2]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Date", each [Data]{0}[Column2]), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Type", each [Data]{2}[Column2]), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Checked by", each [Data]{Table.RowCount([Data])-1}[Column2]) in #"Added Custom4"This yields
Now you will have to decide if you want to expand this with the actual numbers or if those should be put into a separate table and unpivoted.
For example:
let Source = Folder.Files("C:\Users\xxx\Downloads"), #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Mock")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.SelectColumns(Excel.Workbook([Content]),{"Name","Data"})), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data"}, {"Sheet", "Data"}), #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Name", "Sheet", "Data"}), #"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Location", each [Data]{1}[Column2]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Date", each [Data]{0}[Column2]), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Type", each [Data]{2}[Column2]), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Checked by", each [Data]{Table.RowCount([Data])-1}[Column2]), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Cleaned", each Table.UnpivotOtherColumns(Table.PromoteHeaders(Table.SelectRows(Table.RemoveLastN(Table.Skip([Data],4),2), each ([Column1] <> null)), [PromoteAllScalars=true]), {"Evaluation", "Note"}, "Attribute", "Value")), #"Removed Other Columns2" = Table.SelectColumns(#"Added Custom5",{"Name", "Sheet", "Location", "Date", "Type", "Checked by", "Cleaned"}) in #"Removed Other Columns2"which could then expand into