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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors