Time to play with dataflows. I have the following situation. For transactions in foreign currencies I need accurate fx rates. Can a dataflow solve this requirement?
First thing I did was to subscribe for a free plan here:
Then in Power BI service I created my very first dataflow. Basically the flow consists of one simple api call (well described on the fixer page):
And with some basic steps in the Power Query editor I ended up with this:
Now I can link every transaction in foreign currency to my central fx rates table. This is great but there was one important thing missing. I couldn’t figure out a way to store the results like it is possible with the streaming dataset feature.
So I was searching for an alternative solution. Why not use CDS for application to store the data? And then point the Power BI dataflow to the CDS entity:
I created a new entity as I could not find an existing one in the standard cds entities:
Afterwards I created a custom connector to the fixer api:
Next thing I needed was a flow to pull the data in my newly created entity:
And last of all I created another dataflow in Power BI pointing to the CDS entity:
Now I have the result I was looking for. A central fx rates table with historical data.
I have to admit figuring out all of this was fun. And best of all I could solve a real business need.