Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Ale_MCA
New Member

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

Captura.PNG

 
 
 
 
 
 

Example Table B

Ale_MCA_1-1612911937238.png

 

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:

 

234.PNG

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. 

2 ACCEPTED SOLUTIONS
Vera_33
Resident Rockstar
Resident Rockstar

Hi @Ale_MCA 

 

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

View solution in original post

v-alq-msft
Community Support
Community Support

Hi, @Ale_MCA 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

TableA:

d1.png

 

TableB:

d2.png

 

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:

d3.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-alq-msft
Community Support
Community Support

Hi, @Ale_MCA 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

TableA:

d1.png

 

TableB:

d2.png

 

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:

d3.png

 

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
Resident Rockstar
Resident Rockstar

Hi @Ale_MCA 

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.