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:
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:
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:
…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:
Next I have a Create CSV Table action to convert the results returned by the query to csv format:
And finally I take the csv data and save it to a CSV file in OneDrive For Business with a Create File action:
Run the Flow and the CSV file is created:
And I can view the contents in Excel Online:
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…
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.
Thanks – that’s good to know. What are the limits, roughly?
Here’s the limits for Flow:
https://docs.microsoft.com/en-us/flow/limits-and-config
Looks like message size is 100 MB. You can also write row by agonizing row to Excel but then the for loop has limits and writing to Excel will take forever. I had to tell myself at one point: “Flow is not a data pump”
Very useful, thanks! I still have a lot to learn about Flow.
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…
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.
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.
Hi Suhail, look at @PieterVeenstra post
https://veenstra.me.uk/2018/10/17/microsoft-flow-read-large-excel-files-within-seconds-without-creating-tables-using-microsoft-graph/
It’s quite interesting how he overcomes some of the limitations you mention.
I’m not a developer and am trying in this precise moment to build something for myself.
Also would really like to know how to pull csv to sql azure through flow.
Hey Chris, did you have issues with having to add IP addresses multiple times to you’re azure firewall settings?
I did have some strange problems at first but I don’t know the cause and they stopped after a few minutes. I don’t think they were firewall related though.
Export PowerQuery query to CSV
https://excel.city/2018/06/export-powerquery-query-to-csv/
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 ?
I don’t know, sorry.
Hi Chris,
I have got this working – but what is really needed is the ability to pass in a parameter – I managed to add a parameter by clicking on the Parameters “folder”, then using get data to add a “dataset” and then changed the code to use the same pattern as the Server or Database. I put a default value in and it worked in the query, by amending the code to reference the parameter in the normal way (#”paramname”). However I cant find any way to pass in the parameter from the main flow.
This is needed as the real value here is in being able to get hold of data for a specific user / client / invoice or whatever and then use that data to say build an email using the HTML table.
Any ideas – can your contacts help – if you work it out could be the good subject of a blog post!
Hi Andrew,
not sure if I get your request right, but how about storing your “paramname” in a file that you import into PQ as well and reference it there?
Thanks Imke, I have thought of that as a workaround, but it seems a bit clunky.
Good news: On-prem gateway now works 🙂
Is it possible to create workflow that uses an input file (or multiple files) uploaded by my client, apply a transformation to that file by Power Query and save the results as a CSV file with a filename that relates back to the original input file?
Hey Chris, fantastic weblog! This functionality you have featured in this post, is this still available. When I try to use it, it asks for a SQL server. Wondering if I’m looking at the wrong action?
I have series of queries which start from appending large csv files (variable column counts) and it takes 45+ minutes on every refresh (reading from my local HDD). I was hoping maybe I could bring the append piece out and put it on Power Automate as I have heard that the append process is slow process.
Thanks again, Sean
Hi Chris,
My data is on-premises SQL Server databases. I would like to transform data using power query and create CSV table… Is it possible to do or I need Azure SQL database instead of on-premises?
Hi Muhammad
You simply need to install the Power BI gateway to allow Power BI to see your on premise SQL servers. See https://powerbi.microsoft.com/en-us/gateway/