Integrate Custom Function

[Mat42]

So, I've inherited a PBI report that contains a custom function (helpfully named 'Function1'). It pulls data out activity data for a specific timeframe. The person who created the report used to run this function manually everytime they needed it. It creates a table named 'Invoked function'. The data from this table is exported into Excel then the table is deleted.

The thing is, the date range is always the same: Today and the previous 30days.

How do I automate this function so that it runs automatically everytime I refresh the report? Also, how do I set it so that it overwrites the previous invoked function table? Currently, running it more than once creates more tables 'Invoked function 2...3...etc'.

[anilelmastasi · Accepted solution]

Hello Mat42,

You can automate the execution of Function1 and ensure it refreshes correctly without creating multiple "Invoked function" tables by following these steps:

First Step

If Function1 has date parameters, modify it to use dynamic dates instead of requiring manual input.

let
    StartDate = Date.AddDays(Date.From(DateTime.LocalNow()), -30),
    EndDate = Date.From(DateTime.LocalNow())
in
    Function1(StartDate, EndDate)

Second Step

Instead of manually invoking Function1, create a new table that invokes it dynamically:

1. In Power Query Editor, click New Source → Blank Query.
2. Rename this query to something meaningful, e.g., ActivityData.
3. Use the following formula to invoke Function1 automatically:

= Function1(Date.AddDays(Date.From(DateTime.LocalNow()), -30), Date.From(DateTime.LocalNow()))
​

• Click Close & Apply.

Now, every time the report refreshes, ActiviyData will update automatically.

Third Step

If Power BI is creating multiple "Invoked function" tables (e.g.,Invoked function 2, Invoked function 3) , it’s likely because you are manually invoking the function instead of defining a static query. Delete all existing tables from Power Query Editor and use the new ActivityData query instead.

Fourth Step

To automate refreshing, set up a scheduled refresh in Power BI Service after publishing the report.

Did I answer your question? Mark my post as a solution, this will help others!

If my response assisted you in any way, don't forget to drop me a "Kudos.

Kind Regards,

[Mat42]

You are a beautiful human @anilelmastasi. That is exactly what I need.

Thank you so much.

[anilelmastasi]

You are welcome @Mat42 ! Anytime 😊