Lookup value from same table

[Amardeep100115]

Hi 

i wish to be have  Group Values according to ID values in power query Second ID has some values from First ID columns

Sample Data

Please guide & help

[v-juanli-msft]

Hi @Amardeep100115 

If [Group] has number and text type data, please change the formula from mahoneypat as below:

Lookup Group = 
VAR __thisID2 = Table[ID2]
RETURN
    CALCULATE ( MAX( Table[Group] ), FILTER(Table ,Table[ID] = __thisID2 ))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JSpoerry]

Thank you! Why does this work?! I have been trying to solve a similar problem like this: 
Lookup Group =
VAR __thisID2 = Table[ID2]
RETURN
CALCULATE ( MAX( Table[Group] ), Table[ID] = __thisID2 )

and it never worked. Thank you and also I would love to understand why this makes a difference if you know it! 

[Amardeep100115]

i wish to be have in query editor

[parry2k · Accepted solution]

@Amardeep100115 solution attached using power query.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[mahoneypat]

You can use an expression like this in your calculated column to get your desired result.  FYI that you will not be able to have two columns with same name, so I am changing to ID2 for your second ID column:

Lookup Group =
VAR __thisID2 = Table[ID2]
RETURN
    CALCULATE ( AVERAGE ( Table[Group] ), ALL ( Table ), Table[ID] = __thisID2 )

Not sure if you have multiples rows, so I used average, but if single row for each Min, Max, Sum would also work.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[parry2k]

@Amardeep100115 sorry your question is not clear, can you be more specific, what output you are looking for.

[Amardeep100115]

yelllow highlighted column has first column values and i wish to have values from Group column in new column which refer the yellow highlighted ID values i. e. 

ID  Center  Group   ID Results

1        2         30       2     65

3         4        50      5      55

2        5         65      4      50

5         1         55     1      30

like a vlookup in excel

Like this 

Hope i am clear on my query,