Converting the invoked function to Static Data for consolidation

[Anonymous]

Hello Everyone!

Sessions Greetings!

I am trying to create a Power BI Template, that will consolidate the invoked function as static data which can then be referenced within Power Query to generate insights.

I am worried that the current function I have written won't have it as static data, and every time I refresh the Power BI, the newly generated tab will update to the latest information.

This will not allow me to identify a historic trend, so I download the invoked data into an excel, and then reference the folder where I save the data for each day by manually downloading them.

I would like to do all this work within Power Query but I am hitting walls, any help would be much appreciated.

I would like to know if at all is it possible to :

• write a function the result of which would be a static data
• Reference the grouped folder (Other Queries [1]) to generate an appended list that will have consolidated information every day I create a new invoked function table.

I have attached the template for your reference.

It opens as a compressed folder from google drive, please download it on your desktop.

Please let me know if you have any follow up questions.

Sincerely,

Raj

PS: You can use anonymous credentials to connect to the data source as the data is being fetched from publicly available information.

[Jimmy801]

Hello @Anonymous 

also to me this looks like you would need an incremental refresh, and this, as @ImkeF already wrote is almost not possible in Power BI. If you have a data source, that gives you historical data (but with your function you are only querying the latest data) then you could create a list of dates and then invoke your function with different dates, and therefore getting all historical data. If there is no possibilities to do that you would be better of to user other power apps, where you can set up some data flows, that is automatically retrieving your data on daily basis and reading the output with power query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ImkeF · Accepted solution]

Hi @Anonymous ,

it sounds to me as if you're looking for what's often called as "incremental refresh". 
I've published some workarounds here: How to create a Load History or Load Log in Power Query or Power BI – The BIccountant

or here: Create a load history or stage in CDS instead of incremental load in Power BI – The BIccountant

But it's all a bit hacky, as you'll see.

With regards to a function that returns a static value. This is possible, but I question the motivation for it: Why not simply reference the static value?

Anyway: To do this you create a function without a parameter like so:

() =>
let
    Source = #table({"Product", "Amount"}, {{"A", 10}, {"B", 20}})
in
    Source