This is a collection of Power Query / M functions that I've used in the past. I'm using this article as an open collection so that you may benefit from them as well. I expect this list to grow as time goes on, so feel free to check back periodically.
Once downloaded (or copied), you can simply add (paste) them to a new blank query in the Power Query Editor. These functions can be called in other queries by invoking a custom function.
Below this list, I'll explain each one of them individually. They aren't listed in any particular order, so I would recommend ctrl + f to find anything specific you are looking for.
(Clicking the link opens a downloadable word file with the function)
Tenure Function
This returns a number value representing the time passed up until today; either in years, months or days. It takes two parameters:
1. The beginning date as type date.
2. The return period (years, months, days). Defaults to years if not specified.
Example:
= TenureFunction( beginningDate, "years")
This will return the number of years since the variable beginningDate.
Tenure Range Function
This function returns a string category value for a given number of years.
Example:
= TenureRangeFunction( 1.2 )
This will result in a string value of "1-3 Years". This is helpful for when you need to have some sort of grouping for employee tenure ranges. This one has 0-1 year, 1-3 years, and 3+ years.
Convert Date Function
This returns a value in the date format d/m/yyyy given a number or text value in the format "ddmmyy".
Example:
= ConvertDateFunction("010118")
or
= ConvertDateFunction(010118)
Both of these examples will return the date value of 1/1/2018.
Import Spreadsheet With Python Function
This function will use Python to import a spreadsheet given the file path. Your machine needs to have Python installed in order for this code to work. You would use this function if you are trying to connect to an incompatible version of Excel with Power BI.
Example:
= PythonImportFunction("C:\Users\Brent\MyFile.xls")
This will return a table for the given spreadsheet.
For Loop Function
'For Loops' are a bit complicated in M, but they can be done. This function will return either a table or list (default) for a starting number, ending number and iterator. The last, 4th parameter is optional where you can enter 1 to return a table.
Example:
= ForLoopFunction(1, 10, 1, null)
This will return a list of numbers from 1 to 10.
Split Sentence to Words Function
This will take any sentence and convert it to a table of words including a column for the character count of each word. It will remove standard punctuation marks: " , . ! ? ".
The function takes three parameters; the sentence, word case (0 = lower case, 1 = Proper Case, 2 = UPPER CASE), and an optional list of punctuation to remove.
Example:
= SplitSentence("Hello world; my name is Brent!", 1, {";"})
This will return the following table:
Words Count
Hello 5
World 5
My 2
Name 4
Is 2
Brent 5
