So for this challenge, the same dataset was used, but more tricks were needed. There was a number of groupings that needed to be done, some joins, a nested table… yeah. It definitely kicked up a notch!

Here is the video walkthrough:

Preppin’ Data 2023 Week 5

Here is the M code:

let
  Source = Input, 
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers", 
    {
      {"Transaction Code", type text}, 
      {"Value", Int64.Type}, 
      {"Customer Code", Int64.Type}, 
      {"Online or In-Person", Int64.Type}, 
      {"Transaction Date", type text}
    }
  ), 
  #"Split Column by Delimiter" = Table.SplitColumn(
    #"Changed Type", 
    "Transaction Code", 
    Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), 
    {"Transaction Code.1", "Transaction Code.2"}
  ), 
  #"Changed Type with Locale" = Table.TransformColumnTypes(
    #"Split Column by Delimiter", 
    {{"Transaction Date", type datetime}}, 
    "en-GB"
  ), 
  #"Changed DateTime to Date" = Table.TransformColumnTypes(
    #"Changed Type with Locale", 
    {{"Transaction Date", type date}}
  ), 
  #"Extracted Month Name" = Table.TransformColumns(
    #"Changed DateTime to Date", 
    {{"Transaction Date", each Date.MonthName(_), type text}}
  ), 
  #"Renamed TC to Bank" = Table.RenameColumns(
    #"Extracted Month Name", 
    {{"Transaction Code.1", "Bank"}}
  ), 
  #"Grouped by Bank, Date" = Table.Group(
    #"Renamed TC to Bank", 
    {"Bank", "Transaction Date"}, 
    {{"Value", each List.Sum([Value]), type nullable number}}
  ), 
  #"Sorted by Date Asc, Value Desc" = Table.Sort(
    #"Grouped by Bank, Date", 
    {{"Transaction Date", Order.Ascending}, {"Value", Order.Descending}}
  ), 
  #"Grouped by Month" = Table.Group(
    #"Sorted by Date Asc, Value Desc", 
    {"Transaction Date"}, 
    {
      {
        "Rows", 
        each _, 
        type table [Bank = nullable text, Month = text, Value = nullable number, Index = number]
      }
    }
  ), 
  #"Ranked by Month" = Table.AddColumn(
    #"Grouped by Month", 
    "Rank", 
    each Table.AddIndexColumn([Rows], "Rank", 1, 1)
  ), 
  #"Removed Other Columns" = Table.SelectColumns(#"Ranked by Month", {"Rank"}), 
  #"Expanded Rank" = Table.ExpandTableColumn(
    #"Removed Other Columns", 
    "Rank", 
    {"Bank", "Transaction Date", "Value", "Rank"}, 
    {"Bank", "Transaction Date", "Value", "Rank"}
  ), 
  #"Changed Types on Numbers" = Table.TransformColumnTypes(
    #"Expanded Rank", 
    {
      {"Bank", type text}, 
      {"Transaction Date", type text}, 
      {"Value", type number}, 
      {"Rank", Int64.Type}
    }
  ), 
  #"Avg Transaction Value per Rank" = Table.Group(
    #"Changed Types on Numbers", 
    {"Rank"}, 
    {{"Avg Transaction Value per Rank", each List.Average([Value]), type nullable number}}
  ), 
  #"Avg Rank per Bank" = Table.Group(
    #"Changed Types on Numbers", 
    {"Bank"}, 
    {{"Avg Rank per Bank", each List.Average([Rank]), type nullable number}}
  ), 
  #"Join Bank" = Table.Join(#"Changed Types on Numbers", "Bank", #"Avg Rank per Bank", "Bank"), 
  #"Join Rank" = Table.Join(#"Join Bank", "Rank", #"Avg Transaction Value per Rank", "Rank"), 
  #"Renamed Bank Rank Per Month" = Table.RenameColumns(
    #"Join Rank", 
    {{"Rank", "Bank Rank Per Month"}}
  ), 
  #"Reordered Columns" = Table.ReorderColumns(
    #"Renamed Bank Rank Per Month", 
    {
      "Transaction Date", 
      "Bank", 
      "Value", 
      "Bank Rank Per Month", 
      "Avg Transaction Value per Rank", 
      "Avg Rank per Bank"
    }
  ), 
  #"Sorted Rows" = Table.Sort(
    #"Reordered Columns", 
    {{"Transaction Date", Order.Ascending}, {"Bank Rank Per Month", Order.Ascending}}
  )
in
  #"Sorted Rows"
Bitnami