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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jn123
Frequent Visitor

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 NameCode
ACode A
BCode B
CCode C
DCode D
ECode E
FCode F
GCode G
HCode H
ICode I
JCode J

 

Table 2: 

Selection 1Selection 2Selection 3Selection 4Selection 5Selection 6
ACDGFI
BGCAIJ
CDEFG

H

 

 

Desired output: 

Selection 1Selection 2Selection 3Selection 4Selection 5Selection 6
Code ACode CCode DCode GCode FCode I
Code BCode GCode CCode ACode ICode J
Code CCode DCode ECode FCode GCode H

 

 

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

 

Thanks!

1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

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

ronrsnfld_0-1677536676285.png

 

Results

ronrsnfld_1-1677536692939.png

 

 

 

View solution in original post

5 REPLIES 5
wdx223_Daniel
Super User
Super User

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

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

ronrsnfld
Super User
Super User

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

ronrsnfld_0-1677536676285.png

 

Results

ronrsnfld_1-1677536692939.png

 

 

 

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.

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! 🙂

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors