Power Query Archives – The BIccountant

Retrieve header fields like response status from Web.Contents in Power BI and Power Query

Many Power Query function not only return their values as advertised in their function documentation, but on top of that a metadata record. This record is like tag that holds additional information about the returned main value (for more details about this, please check out my friend Lars Schreiber’s article about it).

Useful metadata for the Web.Contents function

Today I discovered that the function Web.Contents delivers a really nice record with a couple of useful information. To retrieve header fields, you have to use the Value.Metadata function, like so for example:

Return header fields like response status from Web.Contents

Interesting metadata from the Web.Contents – function

This might help for some advanced web query tasks.

How to use

If you want to use this in production, you’d probably branch out the logic. So first use Web.Contents and keep that result in a column or variable. Then add another column that references it and return the metadata record.
Apply the logic check on it and create a last column where you finally parse the content from the binary that Web.Content has returned.

Enjoy & stay queryious 😉

Transform a query into a function in Power Query and Power BI

In my previous blogpost I’ve described a method how to extract a substring that follows a certain pattern from a string. In this post I show how to transform a query into a function that can be applied to many rows of a table.

Video how to transform a query into a function

Please check the video for detailed steps. In there I also show how to modify the code. It shall also detect strings with a sequence of just 8 numbers. In the original query, those had to be followed by a minus sign and another number:

Extract pattern string and numbers from text using List.Accumulate in Power Query

A typical task when cleaning data is to extract substrings from string that follow a certain pattern. In this post I’m going to describe a method that uses the List.Accumulate function for it. Extract a pattern string.

Task

I have to extract a payroll key from a description field that starts with 8 number, followed by an “-” and another number.

aölsfdk0125-fds da12345678-0asdf

So I’m after the 12345678-0.

Plan

I plan to approach this by

Performance tip to speed up slow pivot operations in Power Query and Power BI

Pivot operations in are a very handy feature in Power Query but they can slow down refresh performance. So with some bittersweet pleasure I can tell that I found a trick to speed them up. The sweetness comes from the fact that the performance improvement is very significant. But the bitterness comes from the fact that I could have used this for almost 4 years now, but was too blind to realize at the time when I first worked with the code.

Trick to speed up a slow pivot table

This might not work everywhere, but for my tests, it worked really well: Don’t use an aggregation function when you want fast pivoting:

Don’t aggregate when you want a fast pivot in Power Query

But if your data isn’t aggregated on the row- & column values already, you’ll get this error message:

Error when the values are not adequately aggregated

So to make this work, you have to aggregate the values on the axis’ values before.

Let’s walk through the steps:

Walkthrough

How not to miss the last page when paging with Power BI and Power Query

When you use an API with a paging mechanism (like the example from this blogpost), you’ll might work with a field that contains the address for the next page. You can use this to walk through the available chunks until you reach the last element. That last element in the pagination will not contain a next-field or that field will be null.

Paging in Power Query

In Power Query you can use the function List.Generate for it. According the latest function documentation it:

Generates a list of values given four functions that generate the initial value initial, test against a condition condition, and if successful select the result and generate the next value next.

So an intuitive implementation would look like so:

Initial code for paging: Will miss the last element

In the initial step (row 2) the API will be called and returns this record:

Automatically detect and change the types of all columns at once in Power Query

Today I want to share quick tip on how to automatically detect and change all column types at once in Power Query.

Background

Very often, when you expand a column in Power Query that contains a table or some records, the expanded columns will lose their types (like Chris Webb has described here for example). Or you might just have accidently deleted a “Changed Type”-step.

No types on columns

Did you know there is actually a superfast and easy way to do it?

Click the mouse anywhere in the table
Press Ctrl + a (check all)

Check the whole table with Ctrl + a

Go to the Transform-tab ad choose: “Detect Data Type”

Transform with 1 click

Voila: All your columns should have types on them.

They have been automatically been detected by checking the first 100 rows of your table. So if you know that you’re having columns with inconsistent values in them, make sure to check the automatically assigned values.