Forum Discussion

Mat42's avatar
Mat42
Resolver I
1 year ago
Solved

Integrate Custom Function

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'.

Need Help
Show and Tell
  • anilelmastasi's avatar
    anilelmastasi
    1 year ago

    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,

3 Replies

  • anilelmastasi's avatar
    anilelmastasi
    Super User
    1 year ago

    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,