In the Microsoft Flow October 2018 update – link – we got a very interesting feature added to Microsoft Flow – Power Query and as they state –
Also, to better empower our users – we have integrated Power Query directly into the product. Power Query is a data ingestion tool that allows power users to transform, filter, manipulate, and import data into popular data stores like Power BI and CDS. We are adding this same capability to Microsoft Flow that will allow makers to shape data mashups from SQL Server using a Power Query Online experience.
So, I decided to try it out
You can find the Action by searching after “Power Query”
So far, we only have one possible data source– which is the SQL server (but there is more) – so selecting the action gives us this
You can either pick an existing SQL server connection or create a new connection via the dots…
Clicking the Create Query open the Power Query Editor and we can start our transformation/selection of data
And when we click next we get too the Query Editor
Notice that the Server and Database is created as Parameters.
I haven’t tested it but when you right click the pane you actually get the full “Get data” experience – and when you choose one of these you have to use a Enterprise Gateway to use this.
We can now filter the rows
And even go to the advanced editor when you right click the Query name.
You can only return ONE query to your Flow – so be sure to only enable one of your queries.
Getting it into Flow
When we click the Update button we just get back to our flow
And now we need to add an action that uses the result of the Power Query and we can pick between one of the many possible actions
But just to try let’s try and put the data into Power BI
So, I create a streaming dataset in My Workspace –
And remember to turn the Historical data analysis on
Now as the Query can return more than one row we first Add and “Apply to each” step
And then find the Action “Power BI – Add rows to a dataset”
Now we can pick the workspace – dataset we want the rows add to and bind each column to the result from Power Query
Now we are ready to test our flow
And after the connections is validated we can
And the flow starts
We can follow the progress from the Run history
And entering the step – we can check the output
So, as you can see its JSON document.
And we can start to build a report in Power BI
I think this opens for a lot of different opportunities for utilize the incredible Power Query language to extract and transform data from almost any where and then perform actions – like exporting data, sending notifications via e-mail, create task or alerts based on the data and ….
I am pretty sure that we will have great use of this and can’t wait to see what users will use this for.
Happy Power Querying !!!