Function Archives – The BIccountant

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:

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:

Date.Networkdays function for Power Query and Power BI

Today I’m going to share my custom NETWORKDAYS function for Power Query with you that uses the same syntax than its Excel-equivalent.

NETWORKDAYS function

This function’s first 3 parameters work just like the Excel function and there is a 4th parameter that allows adjusting the day on which the week shall start:

Start as date
End as date
optional holidays as list of dates
optional number to change the start of the week from Monday (default: 1) to any other day (2 would mean that the week starts on Tuesday instead)

The function comes with a UI that lets you first choose a table containing the holidays and then choose the column with the holiday dates. Read more

Advanced transformations on multiple columns at once in Power BI and Power Query

You can apply simple transformations to multiple columns at once in Power Query using the UI only. In this article I show how you can apply advanced transformations on multiple columns at once instead. You can also use this to use custom functions instead. And lastly for the ~~lazy~~efficient fans of custom M-functions: You will get a new “TranformAllMyColumnsAtOnceHowILikeIt”-function as well 😉

Background

The Transform-tab in the query editor is sensitive to the columns you select. So if you select multiple number columns for example, some number transformations will be greyed out and are therefore not accessible:

Some symbols are greyed out

So how could I then multiply all my columns by 10 for example, as the symbol for multiplication is greyed out? Read more

Dynamically create types from text with Type.FromText in Power Query and Power BI

In this article I’ll show you how to create types from text in Power Query, enabling you to dynamically change types via functions for example. It’ll come out as a custom Type.FromText function which has been asked for in the comments of this article: https://www.thebiccountant.com/2017/01/09/dynamic-bulk-type-transformation-in-power-query-power-bi-and-m.

Problem

To transform a column to type text can be done like so:

Table.TransformColumnTypes(Source,{{"Column1", type text}})

This transforms the “Column1” from table “Source” to type text. Now, if you want to make the type dynamic and move it to a function parameter like so:

(VariableType as type) =>

Table.TransformColumnTypes(Source,{{"Column1", VariableType}})

This returns a function dialogue as follows:

Text.RemoveBetweenDelimiters function for Power BI and Power Query

While Power Query has a native function to fetch text between 2 delimiters in Power Query, there is no such function that removes the text instead. Therefore I’ve created a custom function Text.RemoveBetweenDelimiter. It even lets you choose to remove the delimiters themselves as well via the optional 4th parameter. (“Text.RemoveBetweenDelimiters “- function)

New Generation Finance – Accounting – Controlling using Microsoft BI stack

Posts in Category ‘Function’