Left Join for tables containing duplicate records

[Antmkjr]

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

[AlB · Accepted solution]

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"

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.