Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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 🙂
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
Hi @Anonymous ,
please look into Power Query in the linked.pbix, I made it up from your examples.
If it helps, please accept as solution and give kudos 🙂
hashtag_pete
Hi,
there are two ways to do it in Power Query:
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
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
Looking for way to do it Power Query.
Not sure if I can use DAX in power query. Could you help ?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
127 | |
109 | |
93 | |
70 | |
67 |