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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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 🙂

 

 

5 REPLIES 5
v-cgao-msft
Community Support
Community Support

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.

vcgaomsft_0-1648447604838.png

And there is no relationship between the two tables.

vcgaomsft_1-1648447660116.png

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

hashtag_pete
Helper V
Helper V

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

hashtag_pete
Helper V
Helper V

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

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
Helper V
Helper V

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
Not applicable

Looking for way to do it Power Query. 

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.