Preppin’ Data 2023 Week One – With Power Query!

I was able to complete this first challenge for this year. They started with some back-to-basics challenges, which was good in terms of practice.

If you would like to do the challenge yourself, here is the link. This is a really great community challenge for building data prep skills.

Here is my video walkthrough:

Solution Video

Lastly, here is my M code for all outputs:

Output 1

let
    Source = Excel.CurrentWorkbook(){[Name="WeekOne"]}[Content],
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Code", type text}, {"Value", Int64.Type}, {"Customer Code", Int64.Type}, {"Online or In-Person", Int64.Type}, {"Transaction Date", type any}}),
    // On the transaction code column, got all characters before the first hyphen
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Transaction Code", each Text.BeforeDelimiter(_, "-"), type text}}),
    // Grouped Rows By Transaction Code and Summed the values
    #"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"Transaction Code"}, {{"Value", each List.Sum([Value]), type nullable number}}),

    // Renamed transaction code column as bank
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Transaction Code", "Bank"}})
in

    #"Renamed Columns"

Output 2

let
    Source = Excel.CurrentWorkbook(){[Name="WeekOne"]}[Content],
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Code", type text}, {"Value", Int64.Type}, {"Customer Code", Int64.Type}, {"Online or In-Person", Int64.Type}, {"Transaction Date", type text}}),
    
    // Extracted text before first hyphen to get bank
    #"Extracted Text Before Hyphen" = Table.TransformColumns(#"Changed Type", {{"Transaction Code", each Text.BeforeDelimiter(_, "-"), type text}}),

    // Extracted date value before time in text
    #"Extracted Text Before Space" = Table.TransformColumns(#"Extracted Text Before Hyphen", {{"Transaction Date", each Text.BeforeDelimiter(_, " "), type text}}),
    // Used French locale to convert to date because of format
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Extracted Text Before Space", {{"Transaction Date", type date}}, "fr-FR"),
    // Extracted Day of Week Name
    #"Extracted Day Name" = Table.TransformColumns(#"Changed Type with Locale", {{"Transaction Date", each Date.DayOfWeekName(_), type text}}),

    // Used simple if statement to translate whether class was online or in-person
    #"Added Online/In-Person Column" = Table.AddColumn(#"Extracted Day Name", "Online/In-Person", each if [#"Online or In-Person"] = 1 then "Online" else "In-Person", type text),

    // Chose columns I needed
    #"Removed Other Columns" = Table.SelectColumns(#"Added Online/In-Person Column",{"Transaction Code", "Value", "Online/In-Person", "Transaction Date"}),

    // Quick rename to bank and cleaning online/in-person column name
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Transaction Code", "Bank"}, {"Online/In-Person", "Online or In-Person"}}),

    // Grouped the rows and summed
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Bank", "Online or In-Person", "Transaction Date"}, {{"Value", each List.Sum([Value]), type nullable number}}),

    // Sorted ascending
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Value", Order.Ascending}})
in

    #"Sorted Rows"

Output 3

let
    Source = Excel.CurrentWorkbook(){[Name="WeekOne"]}[Content],
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Code", type text}, {"Value", Int64.Type}, {"Customer Code", Int64.Type}, {"Online or In-Person", Int64.Type}, {"Transaction Date", type text}}),
    
    // Extracted text before first hyphen
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Transaction Code", each Text.BeforeDelimiter(_, "-"), type text}}),
    
    // Grouped and summed
    #"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"Transaction Code", "Customer Code"}, {{"Value", each List.Sum([Value]), type nullable number}}),
    
    // Sorted on customer code
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Transaction Code", Order.Ascending}, {"Customer Code", Order.Ascending}}),
    
    // Quick rename to bank
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Transaction Code", "Bank"}})

in

    #"Renamed Columns"
Bitnami