Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Lookup value from same table

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

Need Help
Show and Tell
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous 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.⚡

7 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      i wish to be have in query editor
      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        Anonymous 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.⚡

    • JSpoerry's avatar
      JSpoerry
      Regular Visitor
      3 years ago

      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! 

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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, 

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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