The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 only have saldo for each vendor
for example I have vendor Apple
i need to link ids to each other, but the thing is that one id can be used as linked id only for the first found row within specific vendor
this is an initial dataset:
first row should be linked to the second one
second row should be linked to the first one
third row should not be linked to any row since the list does not have corresponding entries which have not been paired yet
this is my query:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Vendor name", type text}, {"Amount", Int64.Type}, {"Date", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0,1,Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Linked Id", each
let
// Get the current row's vendor name, amount, and index
CurrentVendor = [Vendor name],
CurrentAmount = [Amount],
CurrentIndex = [Index],
// Filter for rows with the opposite amount and the same vendor that occur before and after the current row
FilteredRowsBefore = Table.SelectRows(#"Added Index", each [Vendor name] = CurrentVendor and [Amount] = -CurrentAmount and [Index] < CurrentIndex),
FilteredRowsAfter = Table.SelectRows(#"Added Index", each [Vendor name] = CurrentVendor and [Amount] = -CurrentAmount and [Index] > CurrentIndex),
// Find the first unpaired linked transaction before and after the current row, if any
FirstUnpairedLinkedRowBefore = if not Table.IsEmpty(FilteredRowsBefore) then Table.Last(FilteredRowsBefore) else null,
FirstUnpairedLinkedRowAfter = if not Table.IsEmpty(FilteredRowsAfter) then Table.First(FilteredRowsAfter) else null,
// Get the ID of the first unpaired linked transaction found in each direction, if any
LinkedIdBefore = if FirstUnpairedLinkedRowBefore <> null then Text.From(FirstUnpairedLinkedRowBefore[Id]) else null,
LinkedIdAfter = if FirstUnpairedLinkedRowAfter <> null then Text.From(FirstUnpairedLinkedRowAfter[Id]) else null,
// Combine the linked IDs from both directions
CombinedLinkedId = List.Combine({{LinkedIdBefore}, {LinkedIdAfter}})
in
CombinedLinkedId),
Custom1 = Table.TransformColumns(#"Added Custom", {"Linked Id", each Text.Combine(List.Distinct(List.RemoveNulls(_)), ", "), type text})
in
Custom1
here is achieved result with this query:achieved result with my query
the problem is that row 3 should not be paired to row 1 since row 1 and row 2 already form a pair
so linked id for row 3 should be blank
could you please help me out? thank you very much in advance!
Best regards
Solved! Go to Solution.
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
z
Hi, @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
Result
Stéphane
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}}), #"Added Custom" = Table.AddColumn(ChangedType, "N", each Number.Abs([Amount])), #"Grouped Rows" = Table.Combine(Table.Group(#"Added Custom", {"Vendor name", "N"}, {"T", each let tocol= Table.ToColumns(_) in Table.FromColumns(List.RemoveLastN(tocol)&{List.Combine( List.Transform(List.Split(tocol{0},2),(i)=>if List.Count(i)=1 then {null} else List.Reverse(i)))},Table.ColumnNames(Source)&{"Link"}) })[T]), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Id", Order.Ascending}}) in #"Sorted Rows"
Hi, @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
Result
Stéphane
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
z
thank you! it works fine on a small dataset, do you have an idea how i can write it in a more effective way for a bigger dataset (350.000 rows)?
thanks in advance!
thank you very much! it works!
can you please explain how this recursive function works?
@olena2212 "s" and "c" are lists of rows of our table. We start with full list (step "rows") and empty "c", take first row ("a" step), find a row with the same vendor but opposite amount ("match" step). If found - remove "a" and "match" from "s" and add them into "c" with additional item - "Index ID". When "s" becomes empty, function returns "c". Until then we run function again with updated "s" and "c".
cool!
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
that would be a correct resultcorrect result
Hi,
thank you!
I tried to expand my table with some more rows for test:
expanded table
after this unfortunately one pair is not matched
marked rows were not matched to each other
it seems like it only forms a pair if negative number appears after positive one in the list? can that be?
thanks in advance!
Hi @olena2212 ,
My idea would be to add a step to what you have in your existing implementation, i.e. for each Linked Id keep only the record with the smallest row of Index and change the other records to null.
Here is the whole M function in the Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJBdJGpiDCwMhEz1DPUClWJ1rJCElWF1naGCxtjF3aSM8ELG0CFPDNTC7KL85PKwGyjQ1gKoBGGICVmKIp0UVRA3GEGZoaI7gSYz1TsApzdFOQlZiDlVggOdXEFMkOoAmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Vendor name" = _t, Amount = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Vendor name", type text}, {"Amount", Int64.Type}, {"Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0,1,Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Linked Id", each
let
// Get the current row's vendor name, amount, and index
CurrentVendor = [Vendor name],
CurrentAmount = [Amount],
CurrentIndex = [Index],
// Filter for rows with the opposite amount and the same vendor that occur before and after the current row
FilteredRowsBefore = Table.SelectRows(#"Added Index", each [Vendor name] = CurrentVendor and [Amount] = -CurrentAmount and [Index] < CurrentIndex),
FilteredRowsAfter = Table.SelectRows(#"Added Index", each [Vendor name] = CurrentVendor and [Amount] = -CurrentAmount and [Index] > CurrentIndex),
// Find the first unpaired linked transaction before and after the current row, if any
FirstUnpairedLinkedRowBefore = if not Table.IsEmpty(FilteredRowsBefore) then Table.Last(FilteredRowsBefore) else null,
FirstUnpairedLinkedRowAfter = if not Table.IsEmpty(FilteredRowsAfter) then Table.First(FilteredRowsAfter) else null,
// Get the ID of the first unpaired linked transaction found in each direction, if any
LinkedIdBefore = if FirstUnpairedLinkedRowBefore <> null then Text.From(FirstUnpairedLinkedRowBefore[Id]) else null,
LinkedIdAfter = if FirstUnpairedLinkedRowAfter <> null then Text.From(FirstUnpairedLinkedRowAfter[Id]) else null,
// Combine the linked IDs from both directions
CombinedLinkedId = List.Combine({{LinkedIdBefore}, {LinkedIdAfter}})
in
CombinedLinkedId),
Custom1 = Table.TransformColumns(#"Added Custom", {"Linked Id", each Text.Combine(List.Distinct(List.RemoveNulls(_)), ", "), type text}),
#"Grouped Rows" = Table.Group(Custom1, {"Linked Id"}, {{"Min_Index", each List.Min([Index]), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Linked Id", "Min_Index"}, Table, {"Linked Id", "Index"}, "Table", JoinKind.FullOuter),
#"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Id", "Vendor name", "Amount", "Date"}, {"Id", "Vendor name", "Amount", "Date"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Min_Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Id", "Vendor name", "Amount", "Date", "Linked Id"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Id", Order.Ascending}})
in
#"Sorted Rows"
Your code I did not modify at all, I just added these steps at the end:
#"Grouped Rows" = Table.Group(Custom1, {"Linked Id"}, {{"Min_Index", each List.Min([Index]), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Linked Id", "Min_Index"}, Table, {"Linked Id", "Index"}, "Table", JoinKind.FullOuter),
#"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Id", "Vendor name", "Amount", "Date"}, {"Id", "Vendor name", "Amount", "Date"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Min_Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Id", "Vendor name", "Amount", "Date", "Linked Id"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Id", Order.Ascending}})
in
#"Sorted Rows"
And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks for your answer!
i added some more rows for test
marked rows are linked to 2 other rows
it does not seem to work properly
correct result would look like this:correct result
thank you! I will test it🙂
third row should not be linked to any row since the list does not have corresponding entries which have not been paired yet
why should row 3 not be paired with row 8 ?
Because when we sum row 3 and row 8 it will not result to zero
if row 8 would have 25 then they should have been linked with each other
i search for the same amount but with negative sign and check whether the rows have same vendor then they should form a pair and be linked to each other
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the July 2024 Power BI update to learn about new features.