Merging Queries - Assistance with Duplicates

[Anonymous]

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. 

[Vera_33 · Accepted solution]

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-msft · Accepted solution]

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.

[Vera_33 · Accepted solution]

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])