Refer column from another table in formula

[Anonymous]

Hello Folks,

I have two tables

Table A

UID	Name	Age	B_Percent_Current	DID
1	Donald Trump	50	12.5	1
2	George Bush	52	25	2
3	Kamal Harris	23	9.75	2
4	Zelensky	35	13	1
5	Putin	48	25	3

Table B

DID	Dept Name	Percent Value
1	Admin	1.75
2	Inventory	0.85
3	Sales	2.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 🙂

[v-cgao-msft]

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

[hashtag_pete]

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]

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]

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]

Looking for way to do it Power Query. 

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