Posts in Category ‘Power Query’

List.SelectPositions in Power BI and Power Query

With this new custom function “List.SelectPositions” you can easily select items from a list by just passing a list of their positions within it as the parameter.

What it does

Say you have a list with numbers {1..5} and want to select the 1st, 4th and 5th element from it. Then you can pass these positions to the function as another list: {0, 3, 4}.

ListSelectPositions({1..5}, {0, 3, 4}) will return: {1,4,5}

You see that I’ve decided to follow the zero-based counting principle here, that you find throughout M in the query editor. If you don’t like that, you can use the optional 3rd parameter to let it start to count from 1 instead:

ListSelectPositions({1..5}, {1, 4, 5}, 1) will return {1, 4, 5}

But if you have entered positions that don’t exist, the function will return an error in their positions by default:

ListSelectPositions({1..5}, {1, 4, 5}) will return {2, 5, Error}

because there is no 6th element (you’ve omitted the 3rd parameter that allows you to start counting with 1).

But you can change this behaviour as well through the last optional 4th parameter: Setting it to 0 will fill the missing positions with null like this:

ListSelectPositions({1..5}, {1, 4, 5}, null, 0) will return {2, 5, null}

and setting it to 1 will eliminate it and shorten the list like this:

ListSelectPositions({1..5}, {1, 4, 5}, null, 1) will return {2, 5}

These additional error-handling-options of the 4th parameters are useful for dealing with badly formatted data and if you want to learn more about it, just let me know in the comments so that I can prioritize it.

Function code

Read more

Table.Group: Exploring the 5th element in Power BI and Power Query

In this post I’ll show you the magic stuff you can do with the 5th parameter (the optional comparer function) of the Table.Group M-function in Power BI and Power Query:

Table.Group parameters

  1. table as table,
  2. key as any,
  3. aggregatedColumns as list,
  4. optional groupKind as nullable number,
  5. optional comparer as nullable function

If you’re not familiar with the 4th parameter (groupKind) already, I strongly recommend to read Chris Webb’s article, as we will build on its knowledge here.

Another aspect worth mentioning for the modus in GroupKind.local is the performance aspect: It runs MUCH faster for large datasets than the default-setting. So if you are sure that your data will always be sorted accordingly, you can speed up your grouping-operations considerably. That means: Your data has to be sorted correctly by default. At least for my tests, you would loose the performance-gain once you’d sort your table by an explicit step before.

You can find an overview of comparer functions here.

Case insensitive grouping

Imagine there was a twist in Chris’ dataset and it would look like so:

Table.Group – Modified Source Data

We would probably not be happy with these results then:

Table.Group Problem with Case Sensitivity

Because M is by default case sensitive, we get more groups than we want. Let’s try Comparer.OrdinalIgnoreCase to the rescue then:

Pretty neat, isn’t it 😉  (You can use that comparer in other functions as well, see here for text- and list operations)

Something like this was what I’ve showed Huang Caiguang the other day, who asked me what the 5th parameter of this function was about (or so I understood). He then sent me a link to one of his articles, which demanded a good 2 hours for me to digest and understand: We can also use custom functions to create all different sorts of grouping behaviours here. These are my 2 favourites:

Read more

Remove repeating characters from a string in Power BI and Power Query

Repeating spaces often cause problems when cleaning up your data. My new function “Text.RemoveRepeatingCharacters” can come to the rescue here.

Imagine you have a table like this:

Challenge

To further work with this data, it would often be best if there was just one space between the words and not many.

The following function will do this for you:

Function Text.RemoveRepeatingCharacters

How to use

It takes 2 arguments: The Text/String and the Delimiter. The delimiter is an optional argument and by default set to space ” “. So you can leave it blank if that’s fine for you or enter a different value (like “,” for a comma) if needed.

How it works

It splits the text up into a list using the delimiter from the 2nd parameter (4: TextToList). Where one delimiter directly follows another, the element in the list will be empty. The next step (5: FilterList) then filters the list and removes these empty fields. In the last step (6: Result) the remaining (non-empty) fields will be reassembled, using the delimiter again. That way, just one delimiter will be left.

Edit 28-Jan-2018: While searching the web to see if one of my next blogpost-topics have already been published somewhere else already, I came across Ivan Bond’s blogpost who used this same technique over 2 years ago here: https://bondarenkoivan.wordpress.com/2015/10/11/transform-table-column-using-own-function-in-power-query/ . It’s a very good read and you will also learn how to use a function like this to transform an existing column instead of adding a new one to perform the operation like in my example above, so don’t miss it.

Enjoy & stay queryious 🙂

Date.DatesBetween to retrieve dates between 2 dates in Power BI and Power Query

Today I’m sharing a handy function with you that allows you to retrieve all or just a couple of dates between 2 given dates: Date.DatesBetween.

Usage

This function takes 3 parameters:

  1. From- or Start-date
  2. To- or End-date
  3. A selection of ONE of these intervals: Year, Quarter, Month, Week or Day

All dates will be created at the end of the chosen interval: So if you want to analyse events with a duration for example, where you want to transform your data to show one day per (monthly) event, this function generates month-end-dates for every month within the timespan. Please not that if the To-/End-date is within a month, the last element of the list will NOT be that day, but the day of the end of that month.

The default-value for the 3rd parameter is “Day”, so if you omit the specification, the function will return a list of all days in between.

Read more

Right Aligning Text in Power BI: Format Improvements for Easy Profit&Loss Reports

Edit 14-Dec-2017: Now that we can right-align text measures in PowerBI, a SWITCH-measure like here: http://www.thebiccountant.com/2017/04/24/kpis-in-easy-profit-and-loss-for-powerbi/#comment-719 is the best alternative in my eyes. No need to read on 🙂

As shown in my last part of the Easy P&L-series, Power BI unfortunately still lacks some fundamental formatting options like:

  1. Right aligning text (please vote for it here: Right align text in Power BI – edit 15th Nov: We’re there: Right aligning text is available now: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-november-2017-feature-summary/)
  2. Display numbers in different formats within one column (either to be implemented as a “neutral” format for Switch-measures, where the referenced measures carry the formatting attributes already, or as a part of a formula-based conditional formatting) (Thanks Matt for the voting-link: Conditional format SWITCH measure)

So for the moment I choose between the following workaround-options:

  1. Display %-values in a separate column
  2. Format numbers as text and fill up with spaces so that all end up right aligned
  3. See the suggestion from Matt Allington in the comments below (very nice)

Right aligning text or percentage figure in new column

For both options the preparations are the same:

Read more

Bulk-extracting Power Query M-code from multiple pbix files in Power BI

If you want to audit or analyse the M-code of multiple Power BI pbix-files at once, you start with either:

  1. a from-folder query where you filter all files of interest or
  2. a table with the full file-path-specification of the files to be analysed in “Column1”.

Then you add a column where you call the function that extracts the M-code:

Function to extract the M-code

This code is a variation of Igors function which retrieves the code from an opened pbix-file. So now you can apply it to closed pbix-files as well.

For method 1 you call it like so (as it takes the full string for the file-path as its parameter):

Read more