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

[Ptown]

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 debt is also categorised by the age of the debt (e.g. is it 0-30 days old). There is also a part of the business column which signifies which part of my business the customer owes the debt to.

I want to create new rows that show the change in debt for the matching given customer, part of the business and category of debt. It should be the "Current" minus the "Previous".

Note that sometimes there may be no debt currently or previously for a given customer, so there would be no row. In which case it should be treated as zeros.

I have uploaded an Excel file here that shows some dummy data and the exact output I would like. I need to create the output using power query in Excel.

Dummy data File: https://1drv.ms/u/s!AtJBzwB0OS_Qg0efbmJ1jKFW3uUM?e=9SmHB1

Any help greatly appreciated, thanks

[Jakinta · Accepted solution]

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

[Anonymous · Accepted solution]

try this

[Jakinta · Accepted solution]

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