cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ptown
Helper I
Helper I

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

 

 

 

2 ACCEPTED SOLUTIONS
Jakinta
Solution Specialist
Solution Specialist

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

 

 

 

Jakinta_0-1620957219377.png

 

 

 

View solution in original post

Rocco_sprmnt21
Super User II
Super User II

2 REPLIES 2
Rocco_sprmnt21
Super User II
Super User II

Jakinta
Solution Specialist
Solution Specialist

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

 

 

 

Jakinta_0-1620957219377.png

 

 

 

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors