Forum Discussion
Ptown
5 years agoHelper 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...
- 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 - Anonymous5 years ago
try this
Jakinta
5 years agoSolution 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