Forum Discussion
How tp get Power Query to find a matching value from a different table and pull up a value
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!
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
5 Replies
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
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?
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.
if selection names in table 1 is unique, try this code
NewStep=Table.TransformColumns(Table2,{},each Table1{[Selection Name=_]}?[Code]?)
