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.

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.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors