Power Bi Query Editor - Calculated Column using values from another table

[G_Whit-UK]

Hi,

I have two sets of data ("Transactions Table", and "Costs Table").  I want to be able to add a column to the "Transactions Table" to reflec the processing cost of that particular transaction.  The cost is determined by the transaction type, which is listed in the "Transactions Table" - of which there are two types (A & B).  The base cost for each type is held in the "Cost Table".

The structure of "Cost Table" has the transaction types (A & B) as row headers, with Row 1 reflecting the respective costs.

Simple example of the table structures:

Simple representation of the two tables

I attempted to merge the "Costs Table" into the "Transactions Table" using the full outer join function, but that resulted in the joined columns reflecting null values.  My intension was then to perform an IF statement calculation where if 'Transactions Table'[Type] = "A", 'Costs Table'[A],'Costs Table[B]'.  However this plan didn't work and now I'm a little stuck.

Does anyone have any ideas as to how I can go about building this in Query Editor?

Thanks

[v-lionel-msft]

Hi @G_Whit-UK ,

Is this what you want to get?

If yes, maybe you can do like this:

1. Unpivot all columns in "Cost table":

The you can get this:

2. Merge the wables by "Inner":

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz · Accepted solution]

Hi @G_Whit-UK 

You can Unpivot Columns A and B, and later Merge Queries, please see the attached for ref

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[G_Whit-UK]

Hi @Mariusz 

Thanks for the tip, however the proposed method only partially solves my situation.  I never mentioned in my original post that the "Costs Table" has two additional columns ("C" & "D") which both reflect a daily transaction cost.  I need to be able to bring these daily costs into the "Transactions Table" where I can then multiple the daily costs by the number of days the transaction was active for.

I could use your proposed solution by adding two temporary columns to the Transactions Table and give the value of "C" and "D" for each row under the respective column, and then perform the merge, however I'm wondering if there is an alternative way to acheive the same outcome?

Thanks.