Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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'.
Solved! Go to 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:
= Function1(Date.AddDays(Date.From(DateTime.LocalNow()), -30), Date.From(DateTime.LocalNow()))
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,
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:
= Function1(Date.AddDays(Date.From(DateTime.LocalNow()), -30), Date.From(DateTime.LocalNow()))
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,