Posts in Category ‘Dataflows’

Workaround to connect to Power BI dataflows from Power Query in Excel

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)

General setup

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

Read more

Convert DateTime to ISO 8601 date and time strings in Power Query

Often, when querying APIs it is required to enter date and time filters in ISO 8601 format . Today I show a quick way to convert DateTime to ISO 8601 string, based on an ordinary DateTime field according to the following pattern:

2020-10-11T15:00:00-01:00

This represents the 11th October 3pm in UTC -1 timeszone.

Steps to convert DateTime to ISO 8601

If I enter:

#datetime(2020,10,11,12,0,0)

into the formula bar, it will be converted to :

11/10/2020 12:00:00

Comparing to the desired ISO format the year, month and days are in the wrong order. So using the universal Text.From function will not return the correct result.

Read more

Create a load history or stage in CDS instead of incremental load in Power BI

If you’ve been following my blog for a while, you might have noticed my interest in incremental load workarounds. It took some time before we saw the native functionality for it in Power BI and it was first released for premium workspaces only. Fortunately, we now have it for shared workspaces / pro licenses as well and it is a real live saver for scenarios where the refresh speed is an issue.

However, there is a second use case for incremental refresh scenarios that is not covered ideally with the current implementation. This is where the aim is to harvest and store data in Power BI that will become unavailable in their source in the future or one simply wants to create a track of changes in a data source. Chris Webb has beaten me to this article here and describes in great detail how that setup works. He also mentions that this is not a recommended setup, which I agree. Another disadvantage of that solution is that this harvested data is only available as a shared dataset instead of a “simple” table. This limits the use cases and might force you to set up these incremental refreshes in multiple datasets.

Read more