Forum Discussion
Create a column containing values from another table referenced by column and row
I have two tables:
Customers
Prices
I'd like to add a column named "Prices" to the table Customers. This column would contain prices based on the respective column and row from the table Prices using the Budget and Country from the Customers table.
For example, John would get 10000 (since he has a low budget and buys from China), Melanie 80000 (since she has a high budget and buys from the USA), Kirk 50000 (since he has a medium budget and buys from the USA) etc.
Any ideas would be welcomed 😅
Anonymous
First, unpivot the second table
https://radacad.com/pivot-and-unpivot-with-power-bi
Then you will have Price Country and Value in the price table, after rename
A new column in customer
New Column Customers = maxx(filter(Prices,Prices[price]= (Customers[budget]) && Prices[country]= (Customers[country]) ),Prices[value])
5 Replies
Can you unpivot your Country table? If you can, a FILTER function can quickly do this.
If your data is in Power Query, click on the Prices[Price] field and "Unpivot Other Columns"
If not let us know. The formula becomes much more complex and less dynamic if it has to stay in the format it is in.
Anonymous
First, unpivot the second table
https://radacad.com/pivot-and-unpivot-with-power-bi
Then you will have Price Country and Value in the price table, after rename
A new column in customer
New Column Customers = maxx(filter(Prices,Prices[price]= (Customers[budget]) && Prices[country]= (Customers[country]) ),Prices[value])
amitchandak Thanks a lot! It works perfectly.
