Microsoft Flow – New Approval feature

I really like to try things out. And the new Approval features looks really promising. A very good video can be found here:

 

What caught my attention was this picture- the perfect scenario for a new challenge:

I will try to solve the highlighted items as follows:

View Sent requests and view my history: This can be done by saving all requests and approvals to a SQL Database

PowerBI reporting: Send all requests and approvals to the PowerBI streaming dataset

Hopefully my next post will show the final solution.

 

Solving Real World Scenarios with Microsoft Flow – Challenge No. 3 – Part two

To get things started I use a simple daily schedule – nothing special here:

Then I drag a Data Operations – Compose step on the flow. I use this to calculate the previous day. I have found out that I have to enter the expression with leading and trailing double quotes as otherwise it won’t work.

What I find very strange here is the fact that I can’t edit the item anymore after I have saved it. Seems like a bug to me…

Next comes the HTTP Get step:

The API expects the following format:

http://api.fixer.io/2017-01-01?symbols=USD,GBP

I use the CheckDate variable from the previous step to get the rates for the previous day.

Now it’s time for the new Pars JSON step, input is the body of the HTTP step.

To generate the schema all I have to do is pasting the JSON returned by the API.

For information purposes I send myself a mail with the relevant data.

The result looks like this.

As there are only new rates for weekdays I’m using a condition in the next step. Only if the date returned is equal to the previous day proceed to the last step.

First I have to define a streaming dataset in Power BI. Again this is pretty straight forward.

In Microsoft Flow I have defined the counter part as follows.

And that’s all. The rates are transferred to Power BI on a daily basis and only for weekdays (see the gap between 03/17/17 and 03/17/20.

One problem remains. I don’t know why but Live Tiles show no data for my streaming dataset. A bug?

 

So that’s the story so far. On my wish list I have one big point. Microsoft please provide access to the streaming dataset, e.g. in Power BI Desktop or Power Query …

 

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

So I really like playing around with Microsoft Flow. In this „challenge“ I did basically the following:

  • Calling the fixer api on a daily basis
  • Parse the JSON reponse
  • do some logical tests
  • Send myself an e-mail with the current fx rates
  • Add the content to a Power BI Streaming dataset

Although the result looks quite simple it took me a while to figure things out. I will go through each step in the next posting…

 

Microsoft Flow – Parse JSON messages

As stated here Microsoft Flow now supports Json:

https://flow.microsoft.com/en-us/blog/five-services-json-http/

So I was eager to try it out. Created a simple Flow with the new Action „Parse Json“.  But ran into the following error:

capture

So I did a search in the Microsoft Flow Forum and fount this thread:

https://powerusers.microsoft.com/t5/Flow-Forum/Error-from-Parse-JSON-step-when-Updating-Flow/m-p/21462/highlight/true#M5093

It seems that this new feature is still in ALPHA Status. Hope MS gets this fixed soon.

 

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

This is the second challenge:

+ Periodically get currency exchange rates from a web service

+ Insert the rows in a staging table (SQL Database On-premise)

+ Parse inserted Json Data with a stored procedure and insert results in our final Currency Exchange Rates Table.

The resulting Flow would look like this:

captur1

Let’s get things started:

HTTP: I’m using fixer.io to get my data. No Problem so far.

Insert Row: I have installed and configured the On-premises data gateway and it’s working.

Execute stored procedure: Here comes trouble:

capture

Sadly up to now stored procedures are no supported when using a gateway. But hopefully we get this feature soon as stated here.

I then figured out the following workaround. Create an AfterInsert Trigger on the staging table to transfer my data to the final table. But I had now luck:

capture2

After some investigation, I found this thread. Currently you can’t use the Insert Row action on a table with an existing trigger.

So, this is really bad. At present neither stored procedures nor triggers are supported when dealing with Microsoft SQL Databases via the On-premises data gateway. Challenge lost at least for the moment.