How tp get Power Query to find a matching value from a different table and pull up a value

[jn123]

Hello, 

I am looking to have power query find a value from one table that matches a value from another table, and draw up a specific text from another column. 

For example: 

Table 1: 

Selection Name	Code
A	Code A
B	Code B
C	Code C
D	Code D
E	Code E
F	Code F
G	Code G
H	Code H
I	Code I
J	Code J

Table 2: 

Selection 1	Selection 2	Selection 3	Selection 4	Selection 5	Selection 6
A	C	D	G	F	I
B	G	C	A	I	J
C	D	E	F	G	H

Desired output: 

Selection 1	Selection 2	Selection 3	Selection 4	Selection 5	Selection 6
Code A	Code C	Code D	Code G	Code F	Code I
Code B	Code G	Code C	Code A	Code I	Code J
Code C	Code D	Code E	Code F	Code G	Code H

I feel like the process should be rather simple, but everything I have tried so far doesn't seem to work.

Thanks!

[wdx223_Daniel]

if selection names in table 1 is unique, try this code

NewStep=Table.TransformColumns(Table2,{},each Table1{[Selection Name=_]}?[Code]?)

[ronrsnfld · Accepted solution]

In Power Query

• Read in your two tables
• Add an Index column to Table 2, then Unpivot
• Join the two tables to do the "lookup"
• Sort back to the original order
• Pivot with no aggregation

I used an Excel worksheet for my data source. You can change those first four lines to reflect your actual data sources

let

//Change next four lines to reflect your actual data sources
    Source = Excel.CurrentWorkbook(){[Name="tbl_1"]}[Content],
    #"Table 1" = Table.TransformColumnTypes(Source,{{"Selection Name", type text}, {"Code", type text}}),

    Source2 = Excel.CurrentWorkbook(){[Name="tbl_2"]}[Content],
    #"Table 2" =Table.TransformColumnTypes(Source2, List.Transform(Table.ColumnNames(Source2), each {_, type text})),

//add index column to maintain original row order
//then unpivot
    #"Added Index" = Table.AddIndexColumn(#"Table 2", "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),

//Join the tables to do the "lookup"
    Join = Table.Join(#"Unpivoted Other Columns",{"Value"}, #"Table 1",{"Selection Name"}),
    #"Removed Columns" = Table.RemoveColumns(Join,{"Value", "Selection Name"}),

//Sort back to original order
//Then Pivot with no aggregation and delete the Index column
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Attribute", Order.Ascending}, {"Index", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Code"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

 Original Tables

Results

[jn123]

Thank you so much!! 

I was able to get it to work I think, but it is only showing the first column, Selection 1. It is not populating any columns or codes for Selections 2-6. Any ideas on what I could be doing wrong?

[ronrsnfld]

Probably something in your actual data or how you adapted the code I provided to your exact situation. Clearly, from the screenshots I provided, the code works for two tables identical to what you provided with an Excel Source. But without seeing exactly what you have and what you did, I don't have a clue as to what changes you might need to make.

[jn123]

Ah, nevermind. I realized that some of the data in the Selection Name column of Table 1 did not match what was in Table 2. Got it fixed and my desired output table. Thank you sooo much for this! 🙂