ExcelChallenge1041

ExcelChallenge1041.pq

let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Data", type text}}),
  Tx = Table.TransformColumns(
    #"Changed Type",
    {
      "Data",
      each [
        Category    = Text.Trim(Text.Split(_, ">"){0}),
        Transaction = Text.Trim(List.Last(List.RemoveLastN(Text.Split(_, ":"), 1))),
        Amt         = List.Last(Text.Split(_, ":"))
      ]
    }
  ),
  Expand = Table.ExpandRecordColumn(
    Tx,
    "Data",
    {"Category", "Transaction", "Amt"},
    {"Category", "Transaction", "Amt"}
  ),
  SetTypesAfterExpand = Table.TransformColumnTypes(
    Expand,
    {{"Category", type text}, {"Amt", type number}, {"Transaction", type text}}
  ),
  IfThen = Table.AddColumn(
    SetTypesAfterExpand,
    "Amt2",
    each
      if [Transaction] = "Return" then
        - 1 * [Amt]
      else if [Transaction] = "Promotion" then
        0
      else
        [Amt],
    type number
  ),
  #"Grouped Rows" = Table.Group(
    IfThen,
    {"Category"},
    {{"Amount", each List.Sum([Amt2]), type number}}
  ),
  #"Sorted Rows" = Table.Sort(#"Grouped Rows", {{"Category", Order.Ascending}})
in
  #"Sorted Rows"