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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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