Function Archives – The BIccountant

Convert DateTime to ISO 8601 date and time strings in Power Query

Often, when querying APIs it is required to enter date and time filters in ISO 8601 format . Today I show a quick way to convert DateTime to ISO 8601 string, based on an ordinary DateTime field according to the following pattern:

2020-10-11T15:00:00-01:00

This represents the 11th October 3pm in UTC -1 timeszone.

Steps to convert DateTime to ISO 8601

If I enter:

#datetime(2020,10,11,12,0,0)

into the formula bar, it will be converted to :

11/10/2020 12:00:00

Comparing to the desired ISO format the year, month and days are in the wrong order. So using the universal Text.From function will not return the correct result.

Fortunately, there are a couple of xxx.ToText function in Power Query that allow for dedicated formatting parameters in their 2nd arguments. For example, the function DateTime.ToText can actually return the string in the desired format if you pass a format string as the 2nd parameter:

DateTime.ToText with format parameters

The syntax for these format strings can also be found here.

Last step is to add the time-zone string ( & “-01:00”), as I’ve started from a DateTime value only:

DateTime.ToText(DateTime.From(#datetime(2020,10,11,12,0,0)), "yyyy-MM-ddThh:mm:ss") & "-01:00"

Enjoy & stay queryious 😉

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:

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

New Generation Finance – Accounting – Controlling using Microsoft BI stack

Posts in Category ‘Function’