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

Exception reporting and management for missing fuzzy matches in Power BI

In a previous article I have described how to use Fuzzy merge to match misspelled data to valid categories. With the sample data at hand, all values could be matched to existing categories. But what can you do if there are some entries that cannot be matched to existing categories?

I would create an “unmatched” value to the categories table before loading to the data model and assign the “unmatched”-value to all unmatched entries. Then I would create a measure that counts the entries within the unmatched category. I would then create a data driven alert, based on this measure. That would trigger an e-mail to the person responsible for maintaining the list. Or, if responsibilities are a bit more complex, trigger a flow in Power Automate that could handle certain conditions to be followed as well.

Let’s look at some details:

Add an “unmatched” value to the categories table for exception reporting

First I select the category table and deselect “Enable load”, as I want to use this table only for the match. The table that shall be loaded to the model will contain the value for the unmatched items as well. Therefor I reference this query and create a new query. There I add a new row with the following formula:

Categories & #table({"Column1"}, {{"Unmatched"}})

Exception reporting for fuzzy matching

 

Replace unmatched values

Read more

Extract only letters from a mixed string in Power Query and Power BI

This is a quick method about extracting only letters from a string. It is part of the Week2 “Preppin’ data” challenge.

Task for extracting letters from a string

Image you have a string like so: “10.ROADBIKES.423/01” and would like to extract only “ROADBIKES”.
Power Query actually has a function for this purpose: Text.Select. It takes 2 parameters:

  1. The text to select from
  2. A list of characters that shall be selected

For the given example the code would look like so:

Text.Select( "10.ROADBIKES.423/01", {"A".."Z"})

This function is always case sensitive as there is no optional parameter that accepts a comparer function.

Easy application

Read more

Clean up or harmonize mis- or differently spelled category data with Power Query

A typical problem with data that has been created by manual entries is that category values are often misspelled or missed. So in this article I’m showing a very powerful technique on how to deal with this problem to clean up dirty category data. It was inspired by the “Preppin’ data” challenge whose instructions you can read here.

Task

Categorize dirty data

Read more

Your Oracle data import in Power BI and Power Query is slow?

If you’re using the native Oracle connector in Power Query, you will probably experience a very slow import performance. Thanks to Tristan Malherbe for recommending to use the OleDB-connector in Power Query instead. This speeds up import enormously.

How to create the connection string

If you’re using the OleDb.DataSource connector instead, you have to pass a connection string as the first parameter and an optional query record as the second parameter. To speed it up even more, you should use a FetchSize parameter in the connection string. For me, this didn’t work when I pasted it into the popup-window. So I had to manually add it in the query editor:

OleDB connection to an Oracle database

The “:1521” in the connection string is the port number, which is usually 1521 for Oracle databases.

Read more

How to refresh Power Queries on protected sheets in Excel

When working with Power Query in Excel you might want to refresh Power Queries on protected sheets. But this will not work by default. Using a macro to temporarily unprotect the sheet and protect it again will do the trick. But this requires the password being displayed in the VBA code. So please have in mind that this technique only works for scenarios where you want to prevent accidental changes with the password protection.

Steps to refresh Power Queries on protected sheets

Read more