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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
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.

Top Solution Authors
Top Kudoed Authors