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.

Definition

How it works

This function uses Function.Invoke to create a compact code. In step “CaseFunction” (rows 12-18), a table is created that contains all the elements for a case-selection. The first column contains the case that is selected by the 3rd parameter. The other columns contain the functions and expressions for the cases.

Table with all different elements for each case

Step “Case”  filters that table down to one remaining record:

Case = CaseFunctions{[Case = TimeInterval]},

It uses a special row-selector { [NameOfTheColumn = Condition] } that only works for columns containing unique keys.

Step “DateFunction” has the command that creates the list of dates:

DateFunction = List.Transform({0..Case[NumberOfAddedTIs]-1}, each Function.Invoke(Case[LastDateInTI], {Function.Invoke(Case[TypeOfAddedTI], {From, _})} ))

Starting with creating a list with the length of the number of intervals (yellow). The next evaluation step uses  Function.Invoke (green): This allows us to work with the function name as a variable from our Case-record. So whatever has been selected for “TypeOfAddedTI” (Time Interval) will be executed with From and the respective number from our list as its parameter (like: Date.AddWeeks(From, 2) for the third item of the list if “Week” is selected. The last evaluation-step (orange) will be Function.Invoke(CaseLastDateInTI) which shifts every returned date at the end of its Interval (here: Date.EndOfWeek).

Of course, this coding style is a matter of taste, but I found it worth sharing.

Enjoy & stay queryious 😉

Create a function library in Power BI using M-Extensions

Having the ability to use own M-function the same way than native functions in Power BI and Excel has been one of my biggest wishes for quite some time. So I was more than amazed to see Frank Tonsen’s comment showing a way to do exactly this in PowerBI, that has been available for almost half a year now: M-Extensions as part of the custom connectors.

Unlike custom connectors who show up in the import-dialogue and provide a custom tailored option for importing data or creating queries, M-Extensions don’t show up explicitly anywhere in Power BI: They just do their M(agic) job to make the functions that you’ve defined in them accessible, as if they were inbuilt native functions: Type their name into the formula bar like this (1):

And enjoy the function description (2: if you’ve specified it in the definition, which is optional):

Simplest example

  1. Your functions: Number.Double and Number.Triple
  2. Combined with the keyword “shared” and separated by “;”
  3. Prefix by “section MyLibrary” gives this text:
section MyLibrary;
shared Number.Double = (Number as number) =>
2 * Number;
shared Number.Triple = (Number as number) =>
3 * Number;

 

How to make M-Extensions work

Read more

Number.Mod rescue pack for Power BI and Power Query

If you use the M-function Number.Mod in Power BI or Power Query and expect the same result like in Excel or DAX, you are probably in good company.

But if the signs of the number and the divisor are not the same, M will differ from Excel and DAX:

Number.Mod in M is different

This is by design, so you can use this this formula instead, if you need matching results:

[Number] – [Divisor] * Number.RoundDown( [Number] / [Divisor] )

Enjoy & stay queryious 🙂

How Power Query can return clickable hyperlinks with friendly names to Excel

When you use Power Query as an Excel-automation-tool rather than just to feed the data model, you might want to return clickable hyperlinks that carry friendly names. This doesn’t work out of the box, but with a little tweak it will be fine:

The trick

Return a text-string that contains the Excel (!)-formula for hyperlinks, preceded by an apostrophe  ‘ . After the data has been loaded to the sheet, check the column and replace ‘= by = to activate your Excel-formula:

Activate the HYPERLINK formula by replacing ‘= with =

You can then format the column to “Hyperlink”:

Read more

How to create and use an R-function-library in Power BI

Edit 10-10-2017: There is also a (simpler) way to run a custom function library described here: http://www.thebiccountant.com/2017/10/06/create-a-function-library-in-power-bi-using-m-extensions/ . If you go that route, the only point of interest in the article might be how to create your function library automatically.

Once you’ve discovered the huge potential R gives you to expand your analytical toolbox in Power BI (check some tips & tricks in my previous blogpost if you haven’t already), you might wish to have all your awesome functions conveniently at hand when designing new solutions. And thanks to M, there’s actually nothing easier than that: R-function-library in a record (which works just the same for M-functions 🙂 )

Put your functions into a record (fnr) with the function name as the field name and the function itself as the value: One query to hold them all (and not cluttering your editor pane) and ready to use as if they were native functions:

R-function-library

Use

will export content of my query “Actuals” to csv-file on my desktop.

  1. fnr is the name of the record. You can give it your own name of course, I prefer to keep this as short as possible.
  2. followed in square bracket is the name of the function (record field name)
  3. in ordinary brackets you have the function arguments just like in standard M (record value)

Create record

Read more

Tips and Tricks for R scripts in the query editor in Power BI

Especially if you are new to R, there are some things one needs to know to successfully run R-scripts in the query editor of Power BI. I will share them here along with some tricks that made my R-life in Power BI easier:

How to get started – useful links:

Input:

You can feed multiple tables into the R-script

If you click the icon “R script”, the table from the previous step will automatically be passed as the “dataset” to the R-script. So if you don’t fill in any R-code, this will happen:

image

But if you need the content from other tables as well, you just add them into the square brackets like this:

image

“Documentation” looks like this:

image

You can use parameters in the R-script

Apart from tables, you can also use text strings as parameters in the script. They need to be inserted into the code with preceeding “& and trailing &”:

RExportCsv= R.Execute(“write.csv(dataset,” “&CsvExportPath&” “)”,[dataset=Actuals])

Beware that they must be text. So if you want to pass a number, wrap it into Text.From(…).

You cannot use anything else apart from tables and parameters in the R-script

Well, at least I haven’t managed it Smile

R-life gets easy-peasy if you use M-functions for your R-script

Read more

Should we pipe M?

“Just because you could doesn’t mean you should”… So I’m asking the Power Query and M fans & experts here if we “should” pipe M:

Background: With M you can nest your expressions like in Excel to group commands that belong together. But this has some disadvantages like:

  • Reading:
    • the execution order of the functions doesn’t match the reading order
    • the function name and the arguments are torn apart
  • Writing:
    • if you write an additional function around the existing expression which then fails, it is very laborious to manually delete all the code to go back to the previous state (especially, if you have trailing function arguments)
    • if you later recon that you need an intermediate step of the nested expression and need to split up the statement, the same problems occur

So instead of this code:

we could write it like so:

This code works in M if you have a record (“M”) in your queries that contains Kim Burgess’ cool code and an additional record (“M”) that he has kindly helped me with:

All that still folds!

Honestly, it doesn’t look pretty in my eyes (yet), but it works and eliminates the disadvantages mentioned above. With some help of Expression.Evaluate, we could further clean it up to match the magrittr-style for example, but I’ve been warned to use this function, so not sure what to prefer at the end.

Please let me know your thoughts & stay queryious 😉

File to download: ShouldWePipeM.zip

New M-function: Table.TransformColumnTypesToFirstRowsTypes for PowerBI and PowerQuery

The following function automatically transforms all columns to the types that have been detected in the cells of its first row. Provided they come as: Number, date or text (but you can add additional type conversions if you need them.).

It also has some rough edges: If the first value is empty, the column will be converted to text. Also, it contains the (improved) logic from this article:  So if a date is written in a way that it could also be a number, then it will be converted as a number. To minimize the room for errors here, I’ve converted the values to text first, but this is still something to watch out for. But in very many cases it will just do what you have long been looking for:

Use cases:

  • You don’t want to use the automatic but static/hard coded type-conversion in the 2nd step (because you know you’re table is going to have more columns in the future and you want to cater for proper type-conversion of them as well)
  • You’ve lost your column types due to some other command (like Table.ReplaceValues)

M-Code

Code to download: TableColumnTypesToFirstRowsTypes.txt

 

Enjoy & stay queryious 😉

How to expand a column that cannot be expanded in Power BI and Power Query in Excel

Especially when working with JSON-data, you might end up with a column that has elements of mixed types in it. The expand column – arrows will be missing, but some elements still need to be expanded, like here:

But there is an easy way to fix it:

Transform to expandable column

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type list) then _ else {_} }} )

It transforms the “Column1” from table “Source” by checking, if the content of the each row ( _ ) is of type list and if yes, keep that value ( _ ) and if not, transform it to a list (by framing it into curly brackets {_} )

Syntax for tables

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type table) then _ else #table({“Column1”}, {{_}} ) }} )

Syntax for records

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type record) then _ else [a=_] }} )

File for Subscribers to download: HTExpandColumnThatCannotBeExpanded.zip

This technique should be applied to columns where the expandable elements all have the same structure. If that’s not the case, you should use this technique instead.

Enjoy & stay queryious 😉

Table.TransformColumns – alternative in PowerBI and PowerQuery in Excel

This article shows a trick for a little problem that annoyed me for quite some time: How to get Table.TransformColumns transforming the values of a column with a reference to a value (from the same row) of another column?

1 Replace text by a value from another column

So instead of adding a new column where the “*” is replaced by the value from column “WhildcardValue”, I just want to perform the replacement-operation in the original “Text”-column, so that I don’t have to rename and delete the other columns later:

So far, I always ended up fighting with Table.TransformColumns-function and got quite frustrated because I couldn’t find a way to reference the (row-) value of the other column. As it turns out, I was fighting the wrong target here, because Table.ReplaceValue is actually the saviour for this challenge:

1.1 Check column “Text” -> Transform -> Replace Values

So in (4) we just filled in a dummy-value, which we’re going to replace with a reference to the desired column: “each [WildcardValue]” like this:

Read more