Power Platform Community Forum Thread Details

I am building a flow which ingests two different type of files, both containing FX rates (e.g. USD to EUR, USD to GBP, EUR to SEK, etc.), converts them to the same format and then passes them on to other child flows.

I've managed to do all the data manipulation necessary, but during UAT I have run into two issues:

The two different filetypes contain different pairs. One contains only pairs with USD, the other contains mostly EUR based pairs. E.g. one file has as USD/GBP pair and the other has EUR/GBP.

The output child flows also expect the different type of pairs. One would expect for instance only USD based pairs, only EUR based pairs, or even mixed.

I'm allowed to freely pivot over the USD/EUR rate that's in the file. I have some ideas on how to approach this, but I feel a bit in over my head and hope there's some wizards here with some advice.

My current idea is to initialize a JSON object with all the expected pairs for each output (there's 3 or 4 flavours of FX pairs for 6 output formats) and then fill the FX Rate (float) value using an apply to each loop. Within the apply to each loop I determine whether the desired FX pair is in the source or if it needs to be pivotted on the USDEUR pair. I'm not sure what's the most efficient way to intialize this JSON object, aside from typing it up manually.

There's also one pair, AUD/NZD, that needs to be calculated from USD/AUD and USD/NZD, so there's a double pivot in there. This would result in three types of calculations for FX Rates (no transformation, pivot over USD/EUR and double pivot).

Attached are some sample source files.

Here's another example of expected output csv files (I can only attach 3 files to the post):

USD,CAD,2024/11/18,1.4018
AUD,EUR,2024/11/18,0.6137950556708812
CAD,EUR,2024/11/18,0.6731161786946829
CHF,EUR,2024/11/18,1.0683585363385488
DKK,EUR,2024/11/18,0.13409138518846725
GBP,EUR,2024/11/18,1.1963578033591242
JPY,EUR,2024/11/18,0.00610214227054392
NOK,EUR,2024/11/18,0.08606113273387507
NZD,EUR,2024/11/18,0.555859596150217
PLN,EUR,2024/11/18,0.23192190224756207
USD,EUR,2024/11/18,0.9435742592942064
UYU,EUR,2024/11/18,0.021989612195157455
CLP,EUR,2024/11/18,0.0009702563077575387
COP,EUR,2024/11/18,0.0002147786734378742
NZD,AUD,2024/11/18,0.9056110684089163
CAD,GBP,2024/11/18,0.5626378469758063
GBP,AUD,2024/11/18,1.9491160645657188
USD,GBP,2024/11/18,0.7887057338906853
UYU,USD,2024/11/18,0.023304591004427874
CLP,USD,2024/11/18,0.0010282776349614395
COP,USD,2024/11/18,0.00022762243810945908
SEK,EUR,2024/11/18,0.08653627719640918
CLF,EUR,2024/11/18,36.886610815085234
SGD,EUR,2024/11/18,0.7050016880560418

Another expected output is the Source1 CSV.TXT, but then it's expected as output when the input is source2.

Happy to hear any suggestions. Much appreciated!

Categories:

Business Process Flows

Pivoting FX Rates over a different rate within a CSV / JSON file

Helpful resources

News and Announcements

Quick Links

November 2024 Newsletter…

Community Update Oct 28…

Tuesday Tip #7 Community Profile Tips…

more Community News

Leaderboard

Featured topics

Product updates