Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
diegoadum
Helper I
Helper I

Join tables with missing values? or fill the gaps?

Hi folks, I think I'm overcomplicating here so I need your wise support!! 

 

I have 2 tables, one is transactions and the other is currency rates. On the currency rates, I have the rates for a particular period maybe I have only 2 records because the first one belongs to October 2020 and the second one belongs to December 2020. Something like this:

 

C1RTTYC1CRDCC1CRCDC1CRRStd Cost EREffectiveDateYearMonth

CRUSDARA0.013333375.0010/1/2020202012 
CRUSDARA0.0093067107.4512/1/2020202012

 

Sorry for the field names!!. Anyway, as you can see we have 2 records. Now on the other side, we have the transaction table that has many records for each day. So the goal is to use the Rate from the Currency Rates table to get the conversion for each transaction in the transaction table. I've tried different kinds of merge models but every way is pushing me to the fact that I need to create many records in the currency rates tables as different days exist in the transaction table. 

 

I'm wondering if there is a way to join the tables with some kind of >= operator that connects those records without a direct link.

 

BTW, all transactions before December 1st should use 75 USD per ARA, and transactions on December 1st and beyond must use 107.45 USD per ARA.

 

Thanks in advance for the help!!!

4 REPLIES 4
v-henryk-mstf
Community Support
Community Support

Hi @diegoadum ,

 

Do you mean you want to implement the requirements in power query? Can you end the meaning of the specific fields and the expected result you expect, I will answer you as soon as possible.


Best Regards,
Henry

 

Hi Henry, the values will be part of the formula within the Power Query. The whole exercise in focusing on inventory revaluation which is a common problem in countries with high inflation scenarios. So far, I solved the problem by adding as many records as months exist in the year due to my union needs Year as well as Month between the two tables. The main goal is to create a calculated column with the revaluation amount. Thanks!

AlexisOlson
Super User
Super User

Currency conversion can be a big headache. I recommend checking out this article for some best-practice patterns for it: https://www.daxpatterns.com/currency-conversion/

 

In any case, you'll almost certainly want a Date table to work with in your report.

Thank Alexis for your help, but in this case, I'm forced to apply the solution as M language inside Power Query, (I should say that before!! 😉 due to base on this info I need to still work with some other fields. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.