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

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

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors