cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
90_percent
Helper I
Helper I

Data sourceing -Ideas and best Practices

Ok, I have searched through the posts, and I am still confused.

Here is the scenario, I have an app, that uses 7 excel spreadsheets, the spreadsheets are generated every evening.

they are shared on a OneDrive, all users have access to it.

when I update the spreadsheets, (they are overwritten with new data) the data doesn't get updated in the app. I have hit the refresh, and it says its successful, but it is almost like its cached. and nothing changes.

 

If I remove the DataSource and readd it, it updates. (not sustainable)

 

Now I am going to go to a SQL database, and use tables, but that is down the road, once I have it all datapoints, and structure set up.

 

My question after all that is this.

Is there a way to reload the data, or delete the cached data to make it do a call to the spreadsheets?

would it be better to put it into a SharePoint Doc Library? would that speed up the cache reload?

 

am i way out in left field?

 

any ideas and suggestions would be appreciated!

 

thanks

 

Guy (90_percent)

 

1 ACCEPTED SOLUTION

Accepted Solutions

@RandyHayes Thank you!

 

a little more information.

 

I have a list of 3500 stocks (rows) with 30 different attributes (columns) the stocks rarely change, but the attributes change daily.

 The other tables are subsets of this table depending on attributes.

I don't need the historical data, just the recent data. 

You mentioned SharePoint, as in a SharePoint list? or SharePoint document library? and would I need to add to the bottom new rows, or can I just change the attributes of the rows?

 

i appreciate the input!

 

 

View solution in original post

2 REPLIES 2
RandyHayes
Super User
Super User

@90_percent 

First - you are working with Excel.  You have to realize that it is treated as a database once you add the list to your app.  So, it should not be altered by other means unless it is really adding or changing data in the existing list.

You have mentioned that some process is replacing those files every evening.  This is not a good thing for PowerApps.  It is not going to work that way.

 

Your better process is to have the new data either added to the excel file as rows in the file thought a PowerAutomate flow, or have the data put into a more flexible datasource - like SharePoint (no additional license needed) or SQL (Premium license needed).

 

If you think about your Excel as a database once it is in your app, then it makes more sense that you would not replace it with something else.  Add to it or alter within the constraints of the rows and columns of it is fine, but replace it is bad.

 

With Excel, the only way to get PowerApps to recognize a major change to it like you describe is to re-add that file as a datasource.  Otherwise it is not going to work with it.

 

HOWEVER, that said - you also mention what sounds like another form of adding Excel data into your App.  You can add Excel tables as interactive (data can change) or static tables (it is read into the app and retained and not changeable).

 

So, make sure you are bringing it in as a datasource that you can alter and not static.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes Thank you!

 

a little more information.

 

I have a list of 3500 stocks (rows) with 30 different attributes (columns) the stocks rarely change, but the attributes change daily.

 The other tables are subsets of this table depending on attributes.

I don't need the historical data, just the recent data. 

You mentioned SharePoint, as in a SharePoint list? or SharePoint document library? and would I need to add to the bottom new rows, or can I just change the attributes of the rows?

 

i appreciate the input!

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (3,191)