Refreshing powerapps data source, breaks excel query

[barryburke]

Hello everyone

I have a powerapp which has an excel (onedrive for business) data source. The excel file has 3 data queries which i update daily to refresh the data in the excel file.

However, when i refresh the data sources from PowerApps, i find that the excel tables are no longer linked to the queries.

I can refresh all queries and they run just fine, however the data will not longer load to the tables.

When i go into the Name Manager the 3 queries are added called ExternalData_1 or 2 etc.

For example:

I want to know what incidents have been entered into the incident management system for the previous day. I have a query that extracts all incidents entered the previous day and it loads to 'DailyIncidents' Table.

I then connect the file and table as a data source in PowerApps and the data flows through fine. However if i go back into excel, i find that the 'DailyIncidents' table is no longer connected to the 'DailyIncidents' query (if i right click in the table the 'refresh' option is greyed out).

If i double click on the query and go into the query editor i can see that the query is running and updating, just not loading to the 'DailyIncidents' table. To fix this, I have to delete the 'DailyIncidents' table and have the query re load to the same position before i save it down.

Any ideas why this is happening and how i can avoid it?

Thanks

Barry

[RandyHayes · Accepted solution]

@barryburke 

I would recommend that when you use an Excel data source for PowerApps, that you treat the Excel file then as only that...a data source.  PowerApps will do what it needs in the Excel file and will easily "break" any existing formulas, tables, etc.  It is not intended to be that dynamic (i.e. PowerApps and users interacting with the Excel file)

What I would recommend is that instead, you create an additional Excel file and within that new file, connect to the data from the original Excel file (Data->Get Data->From File->From Workbook)

Then you will have an Excel file that you can work with and make formulas, tables, charts, etc. etc. all while preserving the integrity of the Excel file connected to your PowerApp.

I hope this provides some fuel for thought.

[barryburke]

Thanks Randy

While not ideal its at least good to know that its not that i am doing something wrong. I am waiting for my organisation to upgrade our powerapps licence so i can direct connect but until then, excel is how we do it.

Taking your idea, i have now developed a process where the file processed in another location and copied to onedrive, where powerapps can break whatever it likes. I only need to update once daily so adding the step isnt too cumbersome.

Thanks again

Barry

[RandyHayes]

@barryburke 

Sounds good.  I would definitely recommend using the connect to data in Excel and just use that source.  You will not have to ever copy the file again and your other Excel will always be up-to-date.

Of course...if you can move to a SharePoint list or beyond, you're in even better shape from the data aspect.  Plus, the concept of the Excel as a data connection will make more sense.  If your data is in SharePoint and you want it in Excel, you just connect to it in Excel.  This plays to what I said above except you just substitute - If your data is in Excel and you want it in Excel, you just connect to it in Excel.  In other words - Keep the original data as a data source.

[barryburke]

@RandyHayes 

I will keep that all in mind thanks Randy. I have hit up the bosses to get me a few days of someones time, we will see if that pays off.

[Ifeoluwa100]

I like this reply. I think it answered a question I was about to ask on updating an Excel file, if the update will be shown on power apps or what can I do for power apps to update its own record.