Index/Match equivalent (same query)

[huguest]

Hello,

I am trying to add a column that will report a value from a "Parent" record that is in the same table.  In Excel, I would use an Index/Match formula to achieve that, but I cannot figure out a simple way of doing the same in Power Query.  

What I have:

ID          Parent ID          Color

1                                     Red

2                                     Blue

3            1

4            2

What I need:

ID          Parent ID          Color          Parent Color

1                                     Red

2                                     Blue

3            1                                              Red

4            2                                              Blue

Basically, it needs to look for the Parent ID in the ID column and return the Color as the Parent Color.  I can use Merge Queries and merge and expand the same Query, but this method seems to be really slow (obviously, my actual data model is a lot more complex).

Thank you!

[v-chuncz-msft · Accepted solution]

@huguest,

You may try LOOKUPVALUE Function in DAX.

Column =
LOOKUPVALUE ( Table1[Color], Table1[ID], Table1[Parent ID] )

[LivioLanzo]

@huguest

check out: https://community.powerbi.com/t5/Desktop/Parent-Child-Hierarchy-Highest-Parent-using-Power-Query-not...