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
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/1447523
Alright, 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
Alright, thank you, this data seems to be in a usable format now.
I'll take a bit to try it out.
Update: All seems to be working good, I can apply this to other things too, thank you.