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.
This function takes 3 parameters:
- From- or Start-date
- To- or End-date
- 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.
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):
- Your functions: Number.Double and Number.Triple
- Combined with the keyword “shared” and separated by “;”
- Prefix by “section MyLibrary” gives this text:
shared Number.Double = (Number as number) =>
2 * Number;
shared Number.Triple = (Number as number) =>
3 * Number;
How to make M-Extensions work
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 🙂
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:
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”:
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:
will export content of my query “Actuals” to csv-file on my desktop.
- 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.
- followed in square bracket is the name of the function (record field name)
- in ordinary brackets you have the function arguments just like in standard M (record value)
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:
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:
But if you need the content from other tables as well, you just add them into the square brackets like this:
“Documentation” looks like this:
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
R-life gets easy-peasy if you use M-functions for your R-script