Forum Discussion
Merging Queries - Assistance with Duplicates
Hi everyone.
This is my first post and I'm fairly new with Power BI, so I apologize beforehand if I'm asking about something excessively easy or obvious.
I have to tables which I'm looking to merge. Table A holds a record of the invoices, while Table B has costs associated with the invoices. What I want is to have the costs in the same table as the invoices; I tried using a Left Outer type of join through the data in the Number column. The tables look like this:
Example Table A
Example Table B
The problem here is that there are usually serveral rows per invoice in Table A, so when I merge it with Table B, the values get duplicated like this:
I cannot group the rows because I loose other important data from Table A like the product code, which does change per row. So how do I merge both tables in shuch a way that the values from Table B only appear in the first row where there is a coincidence? Is it possible?
Thank you.
Hi Anonymous
It is possible to add the Cost from Table B to only the first row of the same invoice number in Table A, however, I am not sure how you can analyse data after merge the 2 tables in this way...basically, you group Table A with all data as a table, then add the number to the first row, then expand it
#"Grouped Rows" = Table.Group(your previous step, {"your inovice number column"}, {{"allrows", each _, type table}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"your inovice number column"}, TableB, {"your inovice number column"}, "TableB", JoinKind.LeftOuter),
#"Expanded TableB" = Table.ExpandTableColumn(#"Merged Queries", "TableB", {"your cost column"}, {"TableB.Cost"}),
#"Added Custom" = Table.AddColumn(#"Expanded TableB", "Custom", (OT) => [a=Table.AddIndexColumn(OT[allrows],"Index"),
b=Table.AddColumn(a,"Value",each if [Index]=0 then OT[TableB.Cost] else null)][b])Hi, Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
TableA:
TableB:
You may apply the following transformations on 'TableA'. Here are the m codes in 'Advanced Editor'.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyADItEMwCQ6VYnWglI9wKjAgpMAYrMEZVYGiApMIErMIEyDTCocKUoAozsApTPCrMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Date = _t, Due = _t, #"Product Code" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Date", type date}, {"Due", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if List.First( Table.SelectRows(#"Added Index",(x)=>x[Number]=[Number])[Index] )=[Index] then List.Sum(Table.SelectRows(#"Table B",(y)=>y[Number]=[Number])[Log Cost]) else null), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}) in #"Removed Columns"Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous
It is possible to add the Cost from Table B to only the first row of the same invoice number in Table A, however, I am not sure how you can analyse data after merge the 2 tables in this way...basically, you group Table A with all data as a table, then add the number to the first row, then expand it
#"Grouped Rows" = Table.Group(your previous step, {"your inovice number column"}, {{"allrows", each _, type table}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"your inovice number column"}, TableB, {"your inovice number column"}, "TableB", JoinKind.LeftOuter),
#"Expanded TableB" = Table.ExpandTableColumn(#"Merged Queries", "TableB", {"your cost column"}, {"TableB.Cost"}),
#"Added Custom" = Table.AddColumn(#"Expanded TableB", "Custom", (OT) => [a=Table.AddIndexColumn(OT[allrows],"Index"),
b=Table.AddColumn(a,"Value",each if [Index]=0 then OT[TableB.Cost] else null)][b])
v-alq-msftCommunity Support
Hi, Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
TableA:
TableB:
You may apply the following transformations on 'TableA'. Here are the m codes in 'Advanced Editor'.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyADItEMwCQ6VYnWglI9wKjAgpMAYrMEZVYGiApMIErMIEyDTCocKUoAozsApTPCrMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Date = _t, Due = _t, #"Product Code" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Date", type date}, {"Due", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if List.First( Table.SelectRows(#"Added Index",(x)=>x[Number]=[Number])[Index] )=[Index] then List.Sum(Table.SelectRows(#"Table B",(y)=>y[Number]=[Number])[Log Cost]) else null), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}) in #"Removed Columns"Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.