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:
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"
