• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Power Query Custom Functions

You are here: Home / Power Query / Power Query Custom Functions
power query custom functions
September 3, 2020 by Philip Treacy

If you're familiar with using Excel functions (and of course you should be) then you'll know that a function typically takes some input and gives you an output.

The input, for example, could be a range, a number or a piece of text. The output type will vary, the SUM function will output a number, LEFT will output a string.

A custom function in Power Query works in a similar way, an input gives an output, but a custom function allows us to make a calculation for every row in a column, or columns, and create a new column from those results.

A custom function is just a type of query containing steps like any other query, and can be written to do complicated things like scrape data from web pages or retrieve data from a database.

Basically it can be any series of steps that you may want to repeat for a given set of input data.

Let's look at some simple examples.

Custom Function to Square a Number

Let's say we have a column of numbers in a table, and we want to calculate the squares of these numbers.

table of numbers

Click into the table and then create a new query: on the Ribbon -> Data -> From Table/Range.

The table is loaded into Power Query

numbers in power query editor

Create a new blank query: right click in the Queries area -> New Query -> Other Sources -> Blank Query

create new blank query

Right click on the new query and then on Advanced Editor.

blank query in advanced editor

To create our function we need to specify an input parameter and write the steps to calculate our output.

Our input is specified by the line (TheNumber as number) => which means this query will take an input that will be referred to as TheNumber and it will be of type number.

To calculate the square of TheNumber we just multiply it by itself.

custom function to square a number

Click done and you'll see on screen that you can enter a parameter (input) to test the function. Enter any number and and click Invoke.

Test invoke the custom function

We don't need this invoked function query so right click on it and delete it.

Next, right click on the function named Query 1 and rename it to fxSquared

rename the query

Or click on it and rename it in the Name box on the right hand side of the screen under Properties.

rename query from properties pane

renamed custom function

There are a couple of ways we can use our new function. By clicking on the Add Column section of the Ribbon we can then use either Custom Column, or Invoke Custom Function.

Using a Custom Function in a Custom Column

With the Numbers query selected, click on Add Column and then Add Custom Column, and you'll get the Custom Column dialog box appearing.

add custom column

In the Custom Column Formula area start typing fx and Intellisense should pop up showing our custom function. Press TAB or click on the function name to enter it into the formula.

Now we need to supply the function with an input which will be the numbers from the number column. Type ( and then you can double click on Number in the Available columns list. If our table had more than 1 column they'd all be listed here to choose from.

Now close your parentheses ), click OK to create the custom column

adding a custom column and using a custom function

and your screen should look like this with a new column of squared numbers.

squared numbers

Close and load the query to your worksheet.

Add a Column by Invoking a Custom Function

You can achieve the same result by Invoking a Custom Function : from the Ribbon -> Add Column -> Invoke Custom Function

invoke a custom function

Give the new column a name : Squared

Choose the Function query : fxSquared

Choose the input column for the TheNumber parameter : Number

invoking a custom function

Click OK and you will have a new column called Squared containing the squares of the numbers in the Number column.

Custom Function for Currency Conversion

I have a table of amounts in Australian dollars and I want to convert these to US dollars.

aud amounts

Click in the table then create a new query from the Ribbon -> Data - From Table/Range

aud values

As before, to create a function, Right click in the Queries area -> New Query -> Other Sources -> Blank Query.

Right click on the new query and click on Advanced Editor.

The following image shows the changes to make to create the currency conversion function. It takes one parameter called Amount and uses a value called Rate which is set at 0.73, this is the AUD/USD conversion rate right now.

The function returns the value Amount * Rate

Rename the function to fxConvertCurrency

custom function to convert currency

Add a Column by Invoking a Custom Function

Follow the same steps as before to Invoke a Custom Function

We end up with another column holding the USD values for the AUD amounts in each row.

invoking custom function to convert currency amounts

That's great but by hard coding the exchange rate into the function, it makes it difficult to maintain and it's just bad practice.

It would be better to get the exchange rates dynamically and not hard code any values into the function.

If you have Office365 you could get up to date currency exchange rates by using the STOCKS data type in Excel and reading those values into your query.

Or you could write a query/function to get the rates from an API, or by using VBA or Power Query to scrape a website that provides exchange rates.

I won't go into that process now, let's just say we've got a new table called Currency_Rates with the most up to date exchange rates, like so.

currency amounts and usd conversion rates

After clicking into the Currency_Rates table, create another query From Table/Range.

Now in the real world you'd probably just modify the fxConvertCurrency function at this point. But for the sake of demonstrating, let's duplicate that function instead and we can modify the duplicate.

Right click on fxConvertCurrency and click on Duplicate. Rename the duplicate to fxConvertCurrencies.

duplicate the query

Right click on the new fxConvertCurrencies function and then open the Advanced Editor.

open the advanced editor for the duplicate query

All we need to do is change the code so that the function takes two input parameters, so add Rate as number as the 2nd parameter. Don't forget the comma between the two parameters.

As Rate is now supplied as a parameter, we don't need to declare it in the function so delete the whole line Rate = 0.73, and you should end up with this. Click Done.

modifying the custom function code

Click on the Currency_Rates query and then Add Column -> Invoke Custom Function.

Give the new column a name : USD Amount

Choose the custom function to use: fxConvertCurrencies

Select the columns to use an inputs, in the same order as the input parameters are listed Amount then Rate : Amount and USD Rate

Click OK and you'll have a new column with the various currency amounts converted to USD with the correct conversion rate.

converting multiplecurrencies to usd by invoking custom function

Summary

I've covered some straightforward examples of custom functions, but they can be used for very complicated and useful things.

In subsequent posts I'll be looking at using them to create things like running totals or calculating the percentage of parts compared to a whole, for example, if a customer makes multiple purchases, what is the $ value of each purchase as a percentage compared to the total amount spent?

 

Download the Workbook with Data and Queries

Enter your email address below to download the workbook with the data and code from this post.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook.

power query custom functions
Philip Treacy

Microsoft Power BI Community Super User Logo

AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

More Power Query Custom Functions Posts

delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
grouped running totals in power query

Grouped Running Totals in Power Query

Create grouped running totals with a Power Query custom function. Simple to use and extremely fast. Example workbook with code to download.
quickly create running totals in power query

Quickly Create Running Totals in Power Query

Create running totals in Power Query using super fast queries that can process 10's of 1000's of records in the blink of an eye.

More Power Query Posts

get started with power query

Get Started with Power Query

10x Productivity with Excel Power Query in 3 easy steps. Get data > Transform Data > Load Data = HOURS Saved!

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.


Category: Power QueryTag: Power Query Custom Functions
Previous Post:data from picture iconImport Data from a Picture to Excel Mobile
Next Post:Excel Custom Number Format Conditions

Reader Interactions

Comments

  1. Zeke

    December 17, 2021 at 4:39 pm

    Thank you for the amazing content!

    Reply
    • Catalin Bombea

      December 18, 2021 at 2:16 pm

      Thank you for your feedback Zeke, appreciate it!
      Feel free please to use our forum as well, if you ever need a hand on excel or office problems.
      Cheers,
      Catalin

      Reply
  2. Jim Fitch

    September 4, 2020 at 5:51 am

    Great post, Phil. I’ve been using PQ custom functions for quite a while now, including some complex ones (but I haven’t strayed outside my workbooks to scrape data from external sites using APIs). I want to develop a library of custom functions. I don’t think Excel/PQ offers a way to store them. My guess is that I should create a text file (or collection of text files), cut scripts from the Advanced Editor, then paste them in the “library” (text file). Are there better ways?

    Reply
    • Catalin Bombea

      September 6, 2020 at 2:32 pm

      Hi Jim,
      PQ can be accessed from visual basic, you can find here some sample codes to manipulate query text: https://gallery.technet.microsoft.com/office/VBA-to-automate-Power-956a52d1

      Reply
    • Philip Treacy

      September 7, 2020 at 10:56 am

      Thanks Jim. That sounds as good a way as any to keep a collection of queries.

      Reply
  3. Prashant

    September 3, 2020 at 9:24 pm

    Amazing learning today,
    can we make the ‘rate’ discussed in second function dynamic

    Reply
    • Philip Treacy

      September 5, 2020 at 3:59 pm

      Thanks Prashant. Isn’t the 2nd part of the 2nd function where the rate is passed into the function as a parameter making it dynamic?

      If that isn’t what you mean can you please explain by using an example.

      Regards

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.