Preppin’ Data 2023 Week 4 – With Power Query!

Preppin’ Data Week 4 saw a common scenario where you had to pull in and make sense of multiple tabs of data from a spreadsheet.

When it comes to this scenario in Power Query, whether it is in Excel or Power BI, there is something you want to do in order to ensure that you always have a clear sense of what was imported: Set aside the source table on the import!

In doing so, if something gets added to the Excel workbook or if you are importing CSVs from a folder and another file is added, it is much easier to keep track of it.

In this demo, I also show how to union the files quickly. You also could have imported all of the tabs individually (by clicking the multiple select on import and checking off each tab). Afterwards, you could have appended all of the tabs as well. However, the method I show is actually faster to do and you get the added bonus of having a table upfront of what can through on import.

Here is the video:

Here is the M code:

let
    
    Source = Excel.Workbook(File.Contents(FilePath), null, true),
    
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "Column4"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4"}),
    
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    
    #"Renamed Tab Column" = Table.RenameColumns(#"Promoted Headers",{{"January", "Tab"}}),
    
    #"Filtered Rows" = Table.SelectRows(#"Renamed Tab Column", each ([Demographic] = "Account Type" or [Demographic] = "Date of Birth" or [Demographic] = "Ethnicity")),
    
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Joining Day", type text}, {"Tab", type text}}),
    
    #"Make Joining Date" = Table.AddColumn(#"Changed Type", "Joining Date", each Date.FromText([Tab] & " " & [Joining Day] & ", 2023"), type date),
    
    #"Removed Other Columns" = Table.SelectColumns(#"Make Joining Date",{"ID", "Value", "Demographic", "Joining Date"}),
    
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Demographic]), "Demographic", "Value"),
    
    #"Changed Types After Pivot" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date of Birth", type date}, {"Account Type", type text}, {"Ethnicity", type text}, {"ID", type text}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Types After Pivot", {"ID", "Account Type", "Date of Birth", "Ethnicity"}, {{"Joining Date", each List.Min([Joining Date]), type date}}),
    
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"ID", "Joining Date", "Account Type", "Date of Birth", "Ethnicity"})

in
    
    #"Reordered Columns"
Bitnami