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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.