Forum Discussion
Ptown
Helper I
5 years agoCreate 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
Anonymous
5 years agoNot applicable
try this