Currently, we cannot connect to Power BI dataflows from Excel Power Query. And as we also cannot use custom connectors in there, we need a different alternative. I’m going to describe how to consume the results from Power BI dataflows as csv-files from Azure Blob Storage. I’m using the “AzureStorage.Blobs” function here, which authenticates with the key of the storage account. So just be aware that this means that you cannot apply role based security here. (Edit: A native connector that supports that is supposed to come soon: Microsoft Power Platform dataflows connector available in Excel – Power Platform Release Plan | Microsoft Docs)
Usually, when a Power BI dataflow is refreshed, a csv-file will be written to a storage in the background that holds the results of a refreshed entity. But you have the option to “bring your own Data Lake” where these files are written to instead. This then allows you to consume the csv files with the “AzureStorage.Blobs” function in Power Query for Excel instead.
Pragmatic Works have a very good tutorial on how to setup the storage in Azure and connect a new Power BI workspace to it as a prerequisite.
Details to connect to dataflows from Excel
Once you’ve connected your dataflow workspace to the blob storage you can perform your first dataflow refresh. This will cause a folder for your workspace being created automatically in the blog storage. More folders will be created inside this folder. They are suffixed “.snapshot” and there will be one for the model.json files and one folder for the csv files of each entity (see picture below).
Each dataflow refresh will add csv files into the snapshot folders with the latest query results. So it’s a good idea to setup a flow or Logic app to automatically get rid of old files that you don’t need any more.
Import csv from Azure Blob Storage
To make sure to always grab the latest data from your storage, you can use my custom function below. This function will also allocate the correct column names and types from the model.json to the csv file. Just notice that complex types are not supported in dataflows, as csv files cannot represent them. Also, currently the duration type is not supported as well and will prohibit to save the dataflow. Also, only use this function in Excel. I will published a modified function for Power BI and dataflows shortly. Using this function in dataflows might even destroy your dataflow !!
You will be prompted to authenticate to the storage account. Choose “Account key” and enter the Account key for the blob storage. You can find that in the “Access key”-section in Azure:
Just fill in the following parameters after calling the function:
- BlobStorageName: Name of your blob storage
- CsvSnapshotFolderName: You can grab this value and the previous BlobStorageName easily if you check the folder in the storage explorer and click on properties:
- optionalLocale: Default is set to “en-US”, change it if needed
- optionalDelimiter: Default encoding is set to comma, change if needed
- optionalEncoding: Default is 1252, change if needed
You need to use this function for each entity/csv file that you want to use from your dataflow.
- As stated before, authentication is on the level of the whole blob storage.
- Using the “AzureStorage.Blobs” function to retrieve the data means that the metadata from all files in that storage account will be downloaded. That’s another reason not to use one storage account for multiple dataflows.
With a proper setup of an own Azure Blob Storage, you can access the results of Power BI dataflows in Excel. A custom function helps to efficiently grab the data and stitches the column names and types from the model.json back to the table.
Being able to consume dataflow results directly from the Azure Blob Storage opens many more use cases. However, authentication is fairly limited today. Feel free to add some votes to the ideas section in the community, like:
Enjoy & stay queryious 😉