Forum Discussion
Solved
Numbers reconciliation in PowerQuery with M language
Hello everyone, i need to reconcile numbers in powerquery within specific vendor name (to link the rows to each other and then i will filter out the paired rows which result to zero), so that i o...
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], rows = List.Buffer(Table.ToRows(Source)), f = (s, c) => [a = s{0}, match = List.Select(s, (x) => {a{1}, a{2}} = {x{1}, -x{2}}){0}? ?? {}, upd_c = if List.IsEmpty(match) then c & {a} else c & {a & {match{0}}} & {match & {a{0}}}, upd_s = List.RemoveMatchingItems(s, {a, match}), next = if List.IsEmpty(s) then c else @f(upd_s, upd_c)][next], upd_rows = f(rows, {}), z = Table.FromList(upd_rows, (x) => x, Table.ColumnNames(Source) & {"Linked ID"}, null) in zHi, olena2212 AlienSx , dufoq3
Another solution with Table.Group
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Abs = Table.AddColumn(Source, "Abs", each Number.Abs([Amount])),
Group = Table.Group(Abs, {"Vendor name", "Abs"},
{{"Data", (x) => let
positive = Table.ToColumns(Table.SelectRows(x, each [Amount]=[Abs])),
negative = Table.ToColumns(Table.SelectRows(x, each [Amount]=-[Abs])),
data = Table.FromColumns(positive & {negative{0}}, Table.ColumnNames(Abs) & {"Linked ID"}) &
Table.FromColumns(negative & {positive{0}}, Table.ColumnNames(Abs) & {"Linked ID"})
in data}}),
Result =
Table.Sort(
Table.SelectRows(
Table.RemoveColumns(
Table.Combine(Group[Data]),
{"Abs"}),
each [Id]<>null),
{{"Id", Order.Ascending}})
in
ResultStéphane
Hi olena2212, check this.
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJBdJGpkqxOtFKRkhCulAxYyxiJkC2b2ZyUX5xfloJkG1sABY2RRPWhYqboYkbQYTN0ZVDxS2QbDSBWGiJ6VZDAyQxUzOIGLKfDA0gxhmi+Aqm0hiLm2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Vendor name" = _t, Amount = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Amount", type number}, {"Id", Int64.Type}}),
fn_LinkedId = (tbl as table)=>
[ a = Table.Buffer(tbl[[Id], [Amount]]),
lg = List.Generate(
()=> [ x = 0,
id = Table.SelectRows(a, (w)=> w[Amount] = a{x}[Amount] * -1)[Id]{0}?,
y = #table({"col1", "col2"}, if id <> null then {{ a{x}[Id], id }} else {}),
z = Table.SelectRows(a, (w)=> not List.Contains(y[col1] & y[col2], w[Id])) ],
each [x] < Table.RowCount(a),
each [ x = [x]+1,
id = [y]{[col2 = a{x}[Id]]}?[col1]? ?? Table.SelectRows([z], (w)=> w[Amount] = a{x}[Amount] * -1)[Id]{0}? ,
y = [y] & #table({"col1", "col2"}, if id <> null then {{ a{x}[Id], id }} else {}),
z = Table.SelectRows([z], (w)=> not List.Contains(y[col1] & y[col2], w[Id])) ],
each [id]
),
b = Table.FromColumns(Table.ToColumns(tbl) & {lg}, Value.Type(ChangedType & #table(type table[Linked Id=Int64.Type],{})))
][b],
GroupedRows = Table.Group(ChangedType, {"Vendor name"}, {{"Fn", each fn_LinkedId(_), type table}}),
CombinedFn = Table.Combine(GroupedRows[Fn])
in
CombinedFn
