Create a column containing values from another table referenced by column and row

[MartinaV]

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 😅

[amitchandak · Accepted solution]

@MartinaV 

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])

[MartinaV]

@amitchandak Thanks a lot! It works perfectly.

[edhans]

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.

[MartinaV]

@edhans Thank you! I didn't know the unpivot function - it makes my life much easier 😊

[edhans]

Great @MartinaV  - the Pivot and UnPivot features are super useful in transforming data into something useful for a Power BI model.