Power Query is a powerful data processing tool that is built into Microsoft Excel and is also available as a standalone product. One of the most useful features of Power Query is the ability to create custom functions in M, the programming language that powers Power Query.
In this article, we will explore how to create a custom function in Power Query M that takes parameters.
What is Power Query M?
Power Query M is a functional programming language used to manipulate and transform data in Power Query. M is used to write formulas, queries, and custom functions in Power Query.
What are Custom Functions in Power Query M?
Custom functions in Power Query M are user-defined functions that can be used to transform data in Power Query. These functions can be reused across multiple queries and workbooks, saving time and effort.
Custom functions in Power Query M can take one or more parameters. Parameters are input values that are passed to the function when it is called.
How to create a Custom Function with Parameters
Creating a custom function in Power Query M that takes parameters is a straightforward process. Here is a step-by-step guide to creating a custom function with parameters:
Step 1: Open the Power Query Editor
The first step is to open the Power Query Editor. This can be done by selecting “Data” from the Excel ribbon, and then selecting “From Table/Range” or “From Other Sources” depending on where the data is located.
Step 2: Create a Blank Query
Once the Power Query Editor is open, create a new blank query by selecting “New Source” and then “Blank Query” from the “Home” tab.
Step 3: Open the Advanced Editor
In the new blank query, select “View” and then “Advanced Editor” from the ribbon. This will open the Advanced Editor window, where you can write your custom function.
Step 4: Define the Function
To create a custom function with parameters, start by defining the function. Here is an example of a simple custom function that takes a text location parameter and uses the free nominatim API:
(it even delays the call for 1 second to keep with their rate limits)
let geocode_location = (location as text) =>
let json_response = Function.InvokeAfter( () => Json.Document(Web.Contents("https://nominatim.openstreetmap.org/search.php?q="""& location &"""&format=jsonv2")),#duration(0,0,0,1))
in geocode_location
Analytics.net.nz 