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

Get certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started

Reply
Antmkjr
Post Patron
Post Patron

Left Join for tables containing duplicate records

https://drive.google.com/file/d/1JJt7iQzBOJpaF0S1KZVSA16-tfhcILiY/view?usp=sharing

 

 

I have a Table A which contains duplicate records, Table B which contains duplicate records. Duplicate records in Both are required and should not be removed.

 

I need to bring in few columns from Table B into Table A,(lookup) but at power query level.

I understand Merge can be performed, but since both tables are having duplicate records it is creating further duplicate records while performing Merge operation.

Basically i dont want the actual no. of rows in Table A to get affected.

 

Operations that I have to perform, at power query level , given below DAX for the same :

 

Lookup Category =
LOOKUPVALUE('Table B'[Category],'Table B'[Concat],'Table A'[Conc ])
 
Lookup Type = LOOKUPVALUE('Table B'[Type],'Table B'[Concat],'Table A'[Conc ])
 
 
1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

Hi @Antmkjr 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZFBDgMhCEXv4nqqiChwFjP3v8YIajOZJk2a9G8gHx+g9h7qUjgCNSaLmCghYB6pFlIY8eW1WPI+FpHDefSAS8PPULhExzBl2C0QmJq1uNWrjq6Gl6lhsctZ2egwfHptEAutRNXJvGTuXB9Tfa4tovLc+AaqypzYvt1XTVEcvr3We938AYvIRNky+B/58w+dFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Conc " = _t, #"Discoverer Amount" = _t, #"Default Effective Date" = _t, Difference = _t, #"AP " = _t, #"Amount on Hold" = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Discoverer Amount", "DA"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Conc "}, Table.Distinct(Table.RemoveColumns(#"Table B", "Amount")), {"Concat"}, "Table B", JoinKind.LeftOuter),
    #"Expanded Table B" = Table.ExpandTableColumn(#"Merged Queries", "Table B", {"Category", "Type"}, {"Category", "Type"})
in
    #"Expanded Table B"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

1 REPLY 1
AlB
Community Champion
Community Champion

Hi @Antmkjr 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZFBDgMhCEXv4nqqiChwFjP3v8YIajOZJk2a9G8gHx+g9h7qUjgCNSaLmCghYB6pFlIY8eW1WPI+FpHDefSAS8PPULhExzBl2C0QmJq1uNWrjq6Gl6lhsctZ2egwfHptEAutRNXJvGTuXB9Tfa4tovLc+AaqypzYvt1XTVEcvr3We938AYvIRNky+B/58w+dFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Conc " = _t, #"Discoverer Amount" = _t, #"Default Effective Date" = _t, Difference = _t, #"AP " = _t, #"Amount on Hold" = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Discoverer Amount", "DA"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Conc "}, Table.Distinct(Table.RemoveColumns(#"Table B", "Amount")), {"Concat"}, "Table B", JoinKind.LeftOuter),
    #"Expanded Table B" = Table.ExpandTableColumn(#"Merged Queries", "Table B", {"Category", "Type"}, {"Category", "Type"})
in
    #"Expanded Table B"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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