Solving Real World Scenarios with the Microsoft Power Platform

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.

 

 

Links, Links, Links

Man möchte eigentlich meinen, in der Sommerzeit hat man mehr Zeit zum Lesen interessanter Beiträge. Bei mir ist es genau umgekehrt, da ich mich in dieser Jahreszeit gerne und viel an der frischen Luft bewege. Trotzdem habe ich mal wieder einige interessante Beiträge zusammen getragen. Ein bunter Reigen, der zeigt, was mich so alles interessiert…

Read and Write Excel files in real-time with R in SQL Server 2017

Power BI Regression Report

API:

https://www.programmableweb.com/news/bing-maps-time-zone-api-now-generally-available/brief/2018/08/15

https://www.programmableweb.com/news/how-to-access-any-restful-api-using-r-language/how-to/2017/07/21

https://www.programmableweb.com/api-university/api-developer-training

 

PowerApps progress bar

Using Azure Functions in PowerApps

SQL table to HTML

 

xRMVirtual User Group

Need Icons?

Microsoft Flow blog

Solving Real World Scenarios with Microsoft Flow – Challenge No. 4

Microsoft Flow has become a tool for daily business for me. Although I’m doing a lot of database related stuff still with SSIS I like to use flow whenever possible. This time I did the following:

+ Watch an ftp folder for new files arriving

+ Parse XML file content into variables

+ Send an e-mail with the result of the XML file to the customer

What really surprised me was the fact that in this special case it was much easier to do this in Flow rather than in SSIS.  For example SSIS is not natively supporting ftps. In flow it is as easy as this:

What I also liked is the fact I could do my work without having to touch the customers server. The final solution looks pretty an lightweight:

The ideas for achieving this I got from this two resources:

http://buildsharepointconcepts.blogspot.com/2018/05/parse-xml-using-microsoft-flow.html

https://baizini-it.com/blog/index.php/2017/12/07/microsoft-flow-uploading-daily-foreign-exchange-rates-from-the-european-central-bank/

 

 

PowerApps and Flow – Learn from the experts

Great video and walk-through showing the capabilities of PowerApps together with Flow:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/help-desk-install?utm_source=twitter&utm_source=t.co&utm_medium=doc&utm_medium=referral&utm_campaign=2018_04_19

Another great article here shows how to generate pdf documents:

http://johnliu.net/blog/2017/10/generate-any-pdf-documents-from-html-with-flow