cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!