Posts in Category ‘M’

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:

Read more

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

Read more

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:

slow pivot

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

Read more

paging pagination Power Query

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:

paging pagination Power Query

Initial code for paging: Will miss the last element

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

Read more

Tips to download files from webpages in Power Query and Power BI

When downloading data from the web, it’s often best to grab the data from APIs that are designed for machine-to-machine communication than from the site that’s actually visible on the screen. Not only is the download usually faster, but you also often get more additional parameters that can be very useful. In this article I’m going to show you how to retrieve the relevant URLs for downloading files from webpages (without resorting to external tools like Fiddler) and how to tweak them to your needs.

Retrieving the URL to download files from webpages

Say I want to download historical stock prices from this webpage:

https://finance.yahoo.com/quote/AAPL/history?p=AAPL

The screen will show a link to a download: Read more

Trimming text with custom characters in Power BI and Power Query

When cleaning dirty data, you might have used the Trim-function (Text.TrimStart or Text.TrimEnd) to delete leading or trailing whitespace from your strings. However, did you know that you could use these functions as well to delete any other characters as well from the start or end of a string? Trimming text with custom characters is pretty straightforward:

Task: Trimming text with custom characters

Say you have a column with values like so

Trimming text with custom characters

and want to delete every number at the end and also every “-” that is directly connected with a number. So that the final output shall look like so:

Trim custom characters at the end of a string.

Optional parameter

By default, you feed just one argument into the Text.TrimStart or Text.TrimEnd function: The string whose whitespace characters shall be removed.

Text.TrimEnd(text as nullable text, optional trim as any) as nullable text

But the second argument lets you define a list of your own characters to be removed respectively. So I can create a list with all the characters that shall be removed from the end like so:

{"0".."9"} & {"-"}

This concatenates 2 lists: The first list contains 10 elements: All numbers as strings. The second list has just one element in it: “-“. I have to put this element into a list as well for being able to use the ampersand (“&”) as an easy concatenator here.

So the full expression for the “Add custom column” dialogue looks like so:

Text.TrimEnd( [MyColumnName], {"0".."9"} & {"-"} )

To see this in action, you can simply paste this code into the advanced editor and follow the steps:

Enjoy and stay queryious 😉