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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.