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.