cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Axylo
Frequent Visitor

Excel Data Table is locked after connecting to Power App

Hi Community

I was searching arount long time but did not find any meaningful hint. So, I try to ask here.

I created a PowerApp which runs pretty good. I'm even able to update data from my source with commands as Patch() etc.

As a data source, I connected Microsoft Excel from OneDrive for Business. The thing is that this Excel grabs data from a CSV-file. So, I depend on regular updates. Now, since I connected the Power App to this Data Table in Excel, I can't update the table with updated values from the CSV anymore. If I have look into the QueryBuilder, it loads the additional values. But the table in Excel does not get updated anymore. Does this sound like regular behaviour? How could I solve that?

Thanks

Axylo

1 ACCEPTED SOLUTION

Accepted Solutions
Axylo
Frequent Visitor

Finally, I found a way how to trick Excel's PowerApp functionality/integration. Good old Excel VBA Macros… 😊 (and poor "new world" features...)!

 

Following steps to success:

  • I added another sheet into the Excel source – « the new sheet »
  • The new sheet is the one having the csv file link in it. The original table (with the right table name [where PowerApp refers to] and the PowerApp IDs) stays untouched and is not linked anymore to PowerQuery.
  • The original table has been extended to 700+ rows, so once PowerApp opens the Excel source, it assigns an ID for all of these rows and they are recognized at any time by PowerApp.
  • Then, I coded a Macro that does the following:
    • It deletes «the new sheet» with the link to csv, but it keeps the PowerQuery
    • It creates a new «Load into » PowerQuery statement that is loading the PowerQuery into a new « the new sheet »
    • It goes and copies all data from «the new sheet» to the original sheet/table, but will not overwrite PowerApp IDs in the table
    • --> so new rows from CSV finally find their way into the Excel table for PowerApp
  • The PowerApp just updates itself by opening and – surprise – finds new entries within the Excel… 😊

 

Hope, this helps anyone who needs it.

View solution in original post

3 REPLIES 3
ACahill
Community Support
Community Support

Hi Axylo,

 

Thank you for raising this.  I'll undertake some testing as the process you've explained and provide my findings and a workaround if I'm able to replicate the issue.  I'll update you with my findings as soon as I've conducted my testing.  

 

Thanks Axylo. 

 

Best regards,

 

Allan 

Microsoft Support 

Axylo
Frequent Visitor

Hi Allan

Did you already find out something?

It keeps locking my Excel-Source even the CSV file show up with more and more entries to be imported into Excel.
Thanks

Alex

Axylo
Frequent Visitor

Finally, I found a way how to trick Excel's PowerApp functionality/integration. Good old Excel VBA Macros… 😊 (and poor "new world" features...)!

 

Following steps to success:

  • I added another sheet into the Excel source – « the new sheet »
  • The new sheet is the one having the csv file link in it. The original table (with the right table name [where PowerApp refers to] and the PowerApp IDs) stays untouched and is not linked anymore to PowerQuery.
  • The original table has been extended to 700+ rows, so once PowerApp opens the Excel source, it assigns an ID for all of these rows and they are recognized at any time by PowerApp.
  • Then, I coded a Macro that does the following:
    • It deletes «the new sheet» with the link to csv, but it keeps the PowerQuery
    • It creates a new «Load into » PowerQuery statement that is loading the PowerQuery into a new « the new sheet »
    • It goes and copies all data from «the new sheet» to the original sheet/table, but will not overwrite PowerApp IDs in the table
    • --> so new rows from CSV finally find their way into the Excel table for PowerApp
  • The PowerApp just updates itself by opening and – surprise – finds new entries within the Excel… 😊

 

Hope, this helps anyone who needs it.

View solution in original post

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Users online (2,244)