Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Refer column from another table in formula

Hello Folks,

 

I have two tables

Table A

UIDNameAgeB_Percent_CurrentDID
1Donald Trump5012.51
2George Bush52252
3Kamal Harris239.752
4Zelensky35131
5Putin48253

 

Table B

DIDDept NamePercent Value
1Admin1.75
2Inventory0.85
3Sales2.00

 

I want to create a custom column in Table A something like,

Custom Column Name = B_Percent_New

if DID = 1 then B_Percent_Current + Percent Value

 

I understand, I can join these two tables on DID column and use any column in the formula field. But, I would like to know if there is any way to refer a column from another table without joining/merging them.

 

I have come across something like Table.select, but couldn't really understand how it is implemented.

 

TIA 🙂

 

 

Need Help
Show and Tell
Tips and Tricks

5 Replies

  • hashtag_pete's avatar
    hashtag_pete
    Icon for Helper V rankHelper V
    4 years ago

    Hi, 

    do you have a relationship between the two tables? If so, you can use RELATED, select the Percent_Value column + B_Percent_Current

    Other option would be LOOKUPVALUE.

     

    Best

    hashtag_pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Looking for way to do it Power Query. 

      Not sure if I can use DAX in power query. Could you help ?

  • hashtag_pete's avatar
    hashtag_pete
    Icon for Helper V rankHelper V
    4 years ago

    Hi, 

     

    there are two ways to do it in Power Query:

    1. #PowerQuery – Replicate doing an Excel VLOOKUP in M – Erik Svensen – Blog about Power BI, Power Apps, Power Query (wordpress.com)

    2. below is a code snippet from what I have done, but I find it hard to copy M Code from one case to another. Basically I used the "Index" to refer to another table, this would be your DID. Then you take the DID of TableA and look in the TableReference (Table B), where each [Index] needs to be replaced by [DID]

    =Table.AddColumn(#"StepBefore", "Column", each Table.AddColumn(#"Step Before", "Column", each (let curIndex = [Index] in Table.SelectRows(TableReference, each [Index] = curIndex)){0}[Column]

     I can do a mock up based on your table later on, but it will take some time as I got some things to do...

    best hashtag_pete

  • hashtag_pete's avatar
    hashtag_pete
    Icon for Helper V rankHelper V
    4 years ago

    Hi Anonymous ,
    please look into Power Query in the linked.pbix, I made it up from your examples. 

    LookUp_PowerQuery.pbix

    If it helps, please accept as solution and give kudos 🙂

    hashtag_pete

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    If you consider using Dax to solve the problem, please create a calculated column.

    B_Percent_New = 
VAR _value = LOOKUPVALUE('Table B'[Percent Value],'Table B'[DID],'Table A'[DID])
RETURN
IF('Table A'[DID]=1,'Table A'[B_Percent_Current]+_value)

    The result looks like this.

    And there is no relationship between the two tables.

    Attach the PBIX file for reference. Hope it helps.

     

    Best Regards,
    Community Support Team_Gao

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data