Table from multiple Excel Sheets

[Anonymous]

Hello, new-ish (about a month) PowerBI user here. I have several Excel workbooks with several sheets, all sharing the same general format shown below: Category A denotes a site by name, the data next to it describes the date the site was evaluated. Category B is a list of locations within that individual site in Category A and their properties.

I am trying to achieve a table of all my Category A sites (from multiple Excel sheets). Certain sites are evaluated more than once (different date, but name under Category A is consistent) so I would like to group them into one cell with a dropdown for the different dates. Something like this.

The Category B data is accessed through a drill-through from the Category A table. This part of the project is already functioning. Due to the nature of this data I cannot change the format of the Excel sheets and more sheets may be added at any given time. Have searched for other guides but have not found anything (other than how to transform combine the sheets, which is not desired, and another guide I do not quite understand)

Thank you.

[lbendlin]

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.

[Anonymous]

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.

[lbendlin]

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-...
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/1447...

[Anonymous]

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.

[lbendlin · Accepted solution]

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

[Anonymous]

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.