Power Query - Append Latest FX Rates Without Overwriting Previous Months

[pi8080]

Hi all,

I have two files: Backlog and Monthly FX Rates.

• Backlog file: This contains all historical FX rates in the "Currency" tab, with dates in column 1 and FX rates for multiple currencies in columns 2 to 16.
• Monthly FX Rates file: Power Automate downloads the latest FX rates every month into the "FX Rate" tab.

Please note the two files have exactly the same currency codes

What I’ve done so far:

In Power Query, I merged these two worksheets and expanded the FX rate table. Now, my table includes:

• Column 1: Dates (from Backlog)
• Columns 2 to 16: FX rates from the Backlog file
• Columns 17 to 31: FX rates from the Monthly FX Rates file

Goal:

I want to copy the latest FX rates from the Monthly FX Rates file into columns 2 to 16 of the Backlog file, for the current and future months. However, when new rates are released each month, the previous months' rates should remain unchanged.

For example:

• Currently, my Backlog file contains FX rates from 1/1/2009 to 1/1/2025.
• The Monthly FX Rates file has rates only for 1/2/2025.
• I need to copy these rates into 1/2/2025 in columns 2 to 16, and all future months after that.
• When March 2025 rates are released, they should be copied from the 1/3/2025 row, but without overwriting the February 2025 rates (which were copied the previous month).

Question:

How can I achieve this in Power Query while ensuring that:

1. The latest FX rates are applied to the current and future months?
2. Previously copied FX rates remain unchanged when new data is added?

Would appreciate any suggestions or best practices!

Thanks

[lbendlin · Accepted solution]

In Power Query, I merged these two worksheets

I hope you meant to say "appended", not "merged"

Goal:
I want to copy the latest FX rates from the Monthly FX Rates file into columns 2 to 16 of the Backlog file, for the current and future months. However, when new rates are released each month, the previous months' rates should remain unchanged.

Power Query has no memory.  It can not do what you describe as the goal. You need to implement this logic further upstream.

Note - Power BI is not Excel.  Unpivot your data to bring it into a usable format.

[JaxonBeckett]

Use conditional merge in Power Query to update future months only.

[JaxonBeckett · Accepted solution]

Use conditional merge in Power Query to update future months only.

[v-mdharahman]

Hi @pi8080,

Thanks for reaching out to the Microsoft fabric community forum.

I wanted to check if you had the opportunity to review the information provided by @lbendlin. If yes and you were able to solve the issue then please mark the helpful reply as solution.

I would also take a moment to thank @lbendlin, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @pi8080,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[v-mdharahman]

Hi @pi8080,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[v-mdharahman]

Hi @pi8080,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[lbendlin · Accepted solution]

In Power Query, I merged these two worksheets

I hope you meant to say "appended", not "merged"

Goal:
I want to copy the latest FX rates from the Monthly FX Rates file into columns 2 to 16 of the Backlog file, for the current and future months. However, when new rates are released each month, the previous months' rates should remain unchanged.

Power Query has no memory.  It can not do what you describe as the goal. You need to implement this logic further upstream.

Note - Power BI is not Excel.  Unpivot your data to bring it into a usable format.