Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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.

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.

Need Help
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    2 years ago

    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

     

     

     

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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.