Merge Tables. Table B has two additional columns.

[Anonymous]

Hello,

I have looked over in similar threads and was unable to find an answer for my case.

I have two Order tables. Table A has quotes in CAD. Table B has quotes in USD.

I have an additional Table C for the Exchange Rates.

I created a relationship between Table B and Table C. Now, in Table B, I have a column for each exchange rate based on the month and year the order was created.

With that, I am capable of generating my revenue converted to CAD. However, I'm unable to join Table A and Table B using Union in DAX or Merge because of the additional columns in Table B.

Table A

Order ID / Created Date / Charges / ...

Table B

Order ID / Created Date / Charges / Exchange Rate / Converted Charges to CAD / ...

My question is: What is the best approach in this scenario? Is there anything I can do to merge only specific columns in a single table? Or should I create a separate excel file, and do my entire calculation there instead of doing it in PBI?

Thank you.

[Anonymous · Accepted solution]

Hello,

Thanks for taking the time to help me @Nathaniel_C  & @v-yuta-msft .

After reading Nathaniel's comment, I took the time to dive into the database to see if I could import it as a single table. Turns out I was able to do it by using an IF function in Power BI.

You guys gave me the insight I needed.

Have a great week.

[v-yuta-msft]

@Anonymous ,

I would suggest you to use dax instead of do that in power query, please refer to artical below:

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

Community Support Team _ Jimmy Tao

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

[Nathaniel_C]

Hi @Anonymous ,

Would you send us a picture of your data model so that we can see the relationships. I am not understanding why you would duplicate columns in table B from table C. What are you ultimately trying to do here?

Thanks

Nathaniel