Forum Discussion

Ptown's avatar
Ptown
Helper I
5 years ago
Solved

Create new rows that show difference between 2 rows that match criteria

I have a table of debt owed by customers. It contains rows showing the current state and the previous state (i.e. how much they owe now, and how much they owed last time the data was downloaded). The...
  • Jakinta's avatar
    5 years ago

    Try this in blank query.

     

     

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUXIEYufSoqLUvBIgyxSIQZQBhAYiSxgzVgeLzoCi1LLM/NJiuHKYTkuECag6jTDsBCJDU4Rq/NowLAQiYwPc+owxrDM3MMBiGS59SPbBNVpgmoCh1wnDi0AM8oQxbn0m2IIGDZkbgB2AXSO2wEHVaYmmExTuzth0WlpaQhgmyBEZCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, #"Part of Business" = _t, #"Current?" = _t, #"NOT DUE" = _t, #"0-30" = _t, #"31-60" = _t, #"61-90" = _t, #"91-120" = _t, #"121-180" = _t, #"181-364" = _t, #"365+" = _t]),
        Cols = Table.ColumnNames(Source),
        RedCols = List.Skip(Cols,3),
        Type = Table.TransformColumnTypes(Source, List.Transform(RedCols, each {_, Int64.Type})),
        ReplacedNulls = Table.ReplaceValue(Type,null,0,Replacer.ReplaceValue,RedCols),
        Group = Table.Group(ReplacedNulls, {"Customer Name", "Part of Business"}, {{"Gr", each Table.SelectColumns( _, List.Skip(Cols,2)), type table }}),
        Trans1 = Table.AddColumn(Group, "Custom", each Table.Transpose([Gr])),
        AddedC = Table.AddColumn(Trans1, "Custom.1", each if List.Count(Table.ColumnNames([Custom])) = 1 then 
    Table.PromoteHeaders(Table.AddColumn([Custom],"Column2", each
    if [Column1] = "Previous" then "Current" 
    else if [Column1] = "Current" then "Previous" else 0))
    else Table.PromoteHeaders([Custom])),
        AddedT = Table.AddColumn(AddedC, "Custom.2", each Table.DemoteHeaders(Table.AddColumn([Custom.1],"Total", each [Current]-[Previous]))),
        Trans2 = Table.AddColumn(AddedT, "Custom.3", each Table.Sort(Table.Transpose([Custom.2]),{"Column1", Order.Ascending})),
        Removed = Table.SelectColumns(Trans2,{"Customer Name", "Part of Business", "Custom.3"}),
        Expanded = Table.ExpandTableColumn(Removed, "Custom.3", Table.ColumnNames(Removed[Custom.3]{0})),
        FINAL = Table.RenameColumns(Expanded, List.Zip({Table.ColumnNames(Expanded),Cols}))
    in
        FINAL