Using Power Query And Microsoft Flow To Automate The Creation Of CSV Files

I was super-excited when, a few days ago, the long-awaited integration of Power Query into Microsoft Flow was announced. I was then gutted when work commitments meant I couldn’t blog about it right away and Erik Svensen beat me to it with this excellent post showing how to push data from SQL Server via Power Query and Flow to a Power BI streaming dataset:

https://eriksvensen.wordpress.com/2018/09/25/powerquery-everywhere-now-in-microsoftflow-as-well/

Anyway, the integration of Power Query into Flow opens up so many interesting possibilities so I couldn’t resist writing about it myself today. For example: something I am asked about a lot is whether it is possible to use Power Query to automate the creation of CSV files. You can’t do this in a supported way in Power Query in Excel or Power BI (and yes I know about all of the slightly hacky ways people have done it like this), but it is absolutely something you can do with Power Query in Flow.

Here’s how:

image

First of all I have a Schedule – Recurrence trigger, which allows you to run a flow on a schedule. In this case I’m setting the Flow to run once a day at 6am UK time:

image

Next I have the all-important Transform data using Power Query action. At the moment you can only use Power Query with SQL Server data sources, and (at least when I tested it) it only seems to work with Azure SQL Database data sources and not on-premises SQL Server databases via a gateway – hopefully this gets fixed soon. Clicking the Edit Query button:

image

…opens up the Power Query online editor, which will be familiar to anyone who has used Power Query in Excel or Power BI Desktop, and you build your query:

image

Next I have a Create CSV Table action to convert the results returned by the query to csv format:

image

And finally I take the csv data and save it to a CSV file in OneDrive For Business with a Create File action:

image 

Run the Flow and the CSV file is created:

image

And I can view the contents in Excel Online:

image

The Power Query/Flow integration is still in Preview and I found a few things didn’t work reliably: for example the first few times I ran my Flow I got errors saying that it couldn’t connect to the Azure SQL Database, even though it clearly could while I was designing the query, but that error went away after a while. What’s more it only works for SQL Server data sources right now and I really hope that it is enabled for all the other data sources that Power Query can connect to, especially Excel. These are just teething troubles though, and it’s clear that this is going to be revolutionary for Power Query and Flow users alike!

I really need to improve my Flow skills now…

18 responses

  1. I have created CSV files in flow but you run into limits when there are large datasets. In fact, doing anything with large datasets involves moving large JSON payloads from one step to another.

  2. Exciting indeed. Looking into writing a custom connector to pull data from BigQuery, massage in PQ (parse json) and insert into PB dataset. If that works, it’s a game changer for us…
    Btw, I have the option to choose a Gateway and the list of gateways show up. Maybe MSFT read your article…

    • The list of gateways shows up, but that doesn’t mean you can actually connect to anything… But why do you need to connect to BigQuery in Flow? Why not do this directly in Power BI?

      • Lot of issues with built-in connector in PBI service: performance, no support for nested or numeric (decimal) type, billing project,… Local ODBC even worse – and no automation. We were looking at using APIs to read from bq and stream to PBI but if Flow can do…

  3. It’s a start but needs to build out more of the connectors available in PBI. I was looking forward to using to convert Excel (on Azure Blob Storage) to JSON/CSV and ingest in SQL with ADF. Can’t seem to get it to open an Excel file from OneDrive, SharePoint Site, or local thru a gateway. It’s exciting they are moving it into Flow, but seems pretty limited at initial launch.

  4. I’m really looking forward to this when I can do it the other way around. Pull Excel or CSV files from a OneDrive/Sharepoint folder, ingest them and land them in SQL Azure. That will open up some serious options for us.

    • Ken, I’ve done this quite a bit in Flow. I read Excel files from Sharepoint. A few things to keep in mind, writing to Excel is row-by-row so get ready for Flow runs for about 5 minutes to write 250 records. I can’t remember if that holds for reading. Hopefully, they will enhance the Excel querying/writing to be batch oritented. CSV doesn’t have that issue but then Excel is forgiving when it comes to columns being added or moved around. Also, if the file is dropped on Sharepoint then it is locked for 10 minutes by Sharepoint so trying to remove or overwrite it won’t work. I had to put in a delay. Finally, the Excel connector hard codes the file path and name at design time and if the files is overwritten then the GUID for the file changes and Flow doesn’t work. Despite all the headaches I still love Microsoft Flow. The Excel connector is so being improved so I’m thinking it’s just a matter of time when all this gets ironed out. It’s just frustrating in the interim.

  5. Pingback: Using Power Query And Microsoft Flow To Automate The Creation Of CSV Files — Chris Webb’s BI Blog | MS Excel | Power Pivot | DAX | SSIS |SQL

  6. Pingback: Integrating Power Query And Microsoft Flow – Curated SQL

  7. I followed your example but in the step ‘create CSV Table’ , in the ‘From’ field I get ‘BODY’ instead of ‘VALUE’ to select … and this does not seem to work further… Any idea what the reason can be ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: