Preppin’ Data 2023 Week Three – With Power Query!

Back with another Preppin’ Data Challenge solution using Power Query in Excel!

This one was a little more complex than the last one. I stuck to the declarative features in Power Query so that anyone newer to prep can follow along. This challenge involved an unpivot, a join and some other trickery to get the data in line.

Here is the video walkthrough:

Here is my M code (Note: This time I did not add comments, but gave steps meaningful names):

Input table:

let
    Source = #"PD 2023 Wk 1 Input",
   
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Code", type text}, {"Value", Int64.Type}, {"Customer Code", type text}, {"Online or In-Person", type text}, {"Transaction Date", type any}}),
   
    #"Filtered on DSB" = Table.SelectRows(#"Changed Type", each Text.StartsWith([Transaction Code], "DSB")),
   
    #"Changed to TD to UK Datetime" = Table.TransformColumnTypes(#"Filtered on DSB", {{"Transaction Date", type datetime}}, "en-GB"),
   
    #"Changed Transact Date to Date" = Table.TransformColumnTypes(#"Changed to TD to UK Datetime",{{"Transaction Date", type date}}),
   
    #"Calculated Quarter" = Table.TransformColumns(#"Changed Transact Date to Date",{{"Transaction Date", Date.QuarterOfYear, Int64.Type}}),
   
    #"Renamed TD to Quarter" = Table.RenameColumns(#"Calculated Quarter",{{"Transaction Date", "Quarter"}}),
   
    #"Replaced 1 with Online" = Table.ReplaceValue(#"Renamed TD to Quarter","1","Online",Replacer.ReplaceText,{"Online or In-Person"}),
   
    #"Replaced 2 with In-Person" = Table.ReplaceValue(#"Replaced 1 with Online","2","In-Person",Replacer.ReplaceText,{"Online or In-Person"}),
   
    #"Grouped by QTR and Type" = Table.Group(#"Replaced 2 with In-Person", {"Quarter", "Online or In-Person"}, {{"Value", each List.Sum([Value]), type nullable number}})

in

    #"Grouped by QTR and Type"

Targets table:

let
    
    Source = Targets,
    
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Online or In-Person", type text}, {"Q1", Int64.Type}, {"Q2", Int64.Type}, {"Q3", Int64.Type}, {"Q4", Int64.Type}}),
    
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Online or In-Person"}, "Attribute", "Value"),
    
    #"Renamed Attr to Quarter" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Quarter"}}),
    
    #"Removed Q from Quarter" = Table.ReplaceValue(#"Renamed Attr to Quarter","Q","",Replacer.ReplaceText,{"Quarter"}),
    
    #"Changed Quarter to Integer" = Table.TransformColumnTypes(#"Removed Q from Quarter",{{"Quarter", Int64.Type}}),
    
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Quarter to Integer",{"Quarter", "Online or In-Person", "Value"})

in

    #"Reordered Columns"

Final table (after merging/joining):

let
    
    Source = Table.NestedJoin(#"Process Input", {"Quarter", "Online or In-Person"}, #"Process Targets", {"Quarter", "Online or In-Person"}, "Process Targets", JoinKind.LeftOuter),
    
    #"Expanded Process Targets" = Table.ExpandTableColumn(Source, "Process Targets", {"Value"}, {"Target.Value"}),
    
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Process Targets",{{"Target.Value", "Quarterly Targets"}}),
    
    #"Calculated Variance" = Table.AddColumn(#"Renamed Columns", "Variance to Target", each [Value] - [Quarterly Targets], type number)

in

    #"Calculated Variance"
Bitnami