Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Table from multiple Excel Sheets

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.

Keworks_0-1695287391696.png

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.

Keworks_1-1695288148679.png

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.

1 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 

lbendlin_0-1695929632827.png

 

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

 

lbendlin_1-1695930409521.png

 

 

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

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
Not applicable

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-...
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
Not applicable

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:

LocationDateChecked 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:

EvaluationNotePoint1Point2Point3Point4
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 

lbendlin_0-1695929632827.png

 

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

 

lbendlin_1-1695930409521.png

 

 

Anonymous
Not applicable

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.