Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
icassiem
Helper V
Helper V

PowerQuery/Automate Create Snapshot of DataSet and store within PBI as Calculated Table

Good day,

 

I have a requirement to variance of previous vs latest, this worked when source was a 1gb csv file

We have an API source to client that only returns latest and client has no data store to store data.

 

I am thinking of way to automate a solution as it would be fully PBI.

 - Can Powerquery take a snapshot of a dataset and store the results within a PBI Calculated table before the API overwrites the latest dataset?

- could i append the table with a snapshot date to control the clearing of old data?

- also schedule the snapshot, example only do the snapshot every 3 months?

 

Or can powerquery write the table results to temp location and then compare a previous vs latest?

 

Please Any Help, i have to make this work?

 

Regards

 

1 ACCEPTED SOLUTION

the answer is still no. Power BI is not a data storage solution. And it doesn't sound like Fabric is an option for you either.

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

yeah, no.  Power BI has no memory* . You need to manage your snapshots in the upstream system. Keep storing your snapshots as CSV.

 

* Yes, there is the semantic model self referencing option in Power Query but that has no safety net, and it will break your brain.

 

Thank you @lbendlin 

 

I've been thinking

1. Have a control callendar table with start of month

2. Control table date = current update 

, make table prev snapshot date statis as Prev

3. Using g powerquery condition when to import data from another dataset, current day = control table with snapshot date and set status = curr

 

Just thinking of logic, but I need to know how to control the clearing of old PREV records etc

 

Any thoughts of my logic thinking?

Still no. Use external storage.

@lbendlin  oh gosh 🙂 

Cleint does not have storage and i need to find an automated method

The client managed to do the prev vs latest ,ethod by maintaining an csv export of all the data, but now we created an API of latest only

 

Can PBI automatically store the data externally by creating/created csv and storing snapshots into the file, other than perhaps configuring the csv file, i would like to make the export to csv automated?

 

or should i rather start of by setting up an excel file that connects to the API and have formulas etc to create the logic of previous but that would also require a manual refresh before importing?

 

there's no system in between 😞

 

any ideas please?

the answer is still no. Power BI is not a data storage solution. And it doesn't sound like Fabric is an option for you either.

Thank You Very Much for Helping @lbendlin 

Not sure how now 😞

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors