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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jaalders
Advocate II
Advocate II

Insert rows question

Hi,

 

I am calling an API and retrieving 1 value, however I want to keep this value in a longer list.

So not actually 1 insertrow, but every e.g. 5 minutes, getting the latest value and add this to the list of old values.

 

I have 2 challenges:
1. how can I insert a new record every time?

2. eventually I can schedule this, or in Power Query schedule as well, but first point 1! 🙂 

 

e.g.:

Current list/table:

123

456

789

 

New value: 521

 

New list:

123

456

789

521

 

5 mins later, new value: 172

 

New list:

123

456

789

521

172

 

etc.

So I need to store the old value and 5 minutes later (or with refresh) store the new value.

I was trying this in Excel with PowerQuery, but maybe PowerBi is better doing this?

 

10 REPLIES 10
BA_Pete
Super User
Super User

Hi @jaalders ,

 

The only way I think you can do this with Power BI is by using incremental refresh in the Power BI Service.

You need a Premium Capacity or Premium-per-User licence.

 

More info:

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview 

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




In PowerQuery you can schedule the query every x mins, so you would suggest using that?
Any idea how? 

Hi @jaalders ,

 

Try having a look through the link I provided to see if it is right for your scenario. It also expains how to do it.

It's the only way I know of to get Power BI to add new snapshot data onto an existing historical dataset.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




I did. but it was not clear to me.
First want to focus how to insert a new row with new data each time....

That should be easy, but I cannot find it hahah!
With RPA it is just an insert when the variable is changing...

 

Also, it is not really incremental.
I just need to ADD new data (1 record) every time... 🙂

Old data just remains.

@jaalders you can take a look at this article on how to use Incremental Refresh to keep existing data in the model while adding new data. However, just as the article itself suggests, it is not recommended for production, just an interesting exercise. Use at your own risk.

The right way to do this is to store your data in another location and incrementally add data to that. Power BI is meant to do a refresh, wipe, refresh, not keep anything. The Incremental Refresh feature is to simply store older unchanged partitions without wiping them out. If you did, a full refresh would restore the same results, just take a long time. It is not meant to keep data in Power BI that doesn't still exist in the source database. Chris Webb's BI Blog: Keep The Existing Data In Your Power BI Dataset And Add New Data To It Using I...



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks!
I just don't understand that MS doesn't allow me to keep static data and add rows from other sources 1 by 1... 

Excel with Power Query or PowerBI, doesn't matter...

 

Will read your article 🙂

Because Power BI isn't a data warehouse. It wasn't designed to be. It is not meant to be the only source of truth, and that is what you are asking it to do. You need to build your data warehouse a different way, then connect Power BI to it. It is a read only reporting tool, not a data repository.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

I think I found a way, look:
https://www.youtube.com/watch?v=xDVUUcbX13g 

Yes, and if you republish your report and lose history, you will lose the data. Again, if circumstances are perfect, you can do this. If not, you will lose your historical data. Proceed with caution. You are using the tool in a way not intended. I hope it works for you.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

I understand, thanks!
Will give it a try and see what happens...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors