Ravindra BagaleCourses & study guides

8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns

8.4 Invoke Custom Function

By Last updated:

Use it to run the same logic in many queries (for example Blinkit and Amazon Now), or to call a function for each row (pagination, Module 5.3).

Steps in Power BI

  1. Home › New Source › Blank Query › Home › Advanced Editor › paste the function below › Done. Rename the query fnDeliveryBand.
  2. Go to the Orders query › Add Column › Invoke Custom Function.
  3. New column name: Delivery Band; Function query: fnDeliveryBand; mins: choose Column Name › Delivery Time Mins › OK.
  4. Set the type to Text. Repeat in the AmazonNow query so both use identical rules.
// fnDeliveryBand
(mins as nullable number) as text =>
    if mins = null then "Not delivered"
    else if mins <= 10 then "0-10 min"
    else if mins <= 15 then "11-15 min"
    else if mins <= 30 then "16-30 min"
    else "30+ min"

Tip

If the business changes the band limits, you edit one function instead of every query.

Practice task

Write fnPaymentGroup(mode as text) as text that returns "Digital" for UPI/Card/Wallet and "Cash" otherwise. Invoke it on Orders.

Ravindra Bagale's Tip

Friends, many students invoke a custom function on a table with thousands of rows that each call a web API, and refresh takes forever. Test the function on a few rows first, keep the function simple, and remember that invoking custom functions usually stops query folding. Don't worry – after doing it two or three times, it becomes a habit.