Improbably Unhelpful Power Query / M Custom Functions that You Might Not Never Use.

November 24, 2018

 

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

Tenure Range Function

Convert Date Function

Import Spreadsheet With Python Function

For Loop Function

Split Sentence to Words 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

 

Please reload

About the Author

Brent Jones loves spending time with his wife and trying out new activities together.  They traveled as digital nomads to many countries in South America, Europe and Asia. He can also speak Japanese!

Our Recent Posts

Please reload

Tags

Address

7000 Central Pkwy #1100, Atlanta GA 30328

Follow

  • Twitter - Black Circle
  • Black Pinterest Icon

©2019 BY ANTARES ANLYTICS. PROUDLY CREATED WITH WIX.COM