Power query workaround

[stuartmacdonald]

Can anybody help me. 2 months of support from Microsoft has got me nowhere...

I have a master document (planner) and a series of individual workbooks (timesheets x 10).

Each individual timesheet uses Power Query to generate the relevant individual tables. These tables are then referenced by Power Apps for each individual timesheet app on their phone.

In theory all good, but in practice, an excel table cannot have multiple connections, so when I change the master document the Power Query updates the query, but does not update the actual table in excel, meaning that Power Apps is referencing a static table.

The official Microsoft line on this is: The issue you are facing is known limitation that the SDK excel connector uses does not support external data connections.

Given the time I have invested into this (100+ hours), I need to find a workaround, but have hit a mental block.
Could I possibly have 2 version of each timesheet, one running the power query the other a mirrored copy which could connect to Power Apps, or would this just run into the same problem, as to create the mirrored copy I need to connect to the table?

Happy to share workbooks if anyone has any ideas....

 

[jkpieterse]

I'm not sure I understand the structure nor the task.
Is this correct:
- Your source data is in the master document
- Each timesheet file extracts data from Master for a particular employee using PQ
- Powerapp of each employee is tied to his own employee file and is supposed to update the PQ table in that file

 

[offthelip]

If the limitation is the connections, what about getting rid of power query for updating the timesheets and do that using VBA instead, then this would "free up" the one connection to the timesheets workbook for use by the phone App.

 

[stuartmacdonald]

Here is an image showing the crux of the problem.
You can see in the 'peek' on the right-hand side that the Power Query is updating from the source, but the table remains static after I make the connection to PowerApps.

https://www.dropbox.com/s/fglb8c1azovrykb/PQ%20eg.png?dl=0

Offthelip: I have used Power Query because its simple, I don't know how to use VBA, but if that would work then I need to learn. Could you give me any broad pointers to get me on my way?

 

[jkpieterse]

If you click Refresh on that connection, does the data update then?

 

[stuartmacdonald]

Nope, I have tried all refresh options and Microsoft has indicated it's an issue with the connector.... I'm looking for a workaround...

 

[jkpieterse]

What does the PQ connection do precisely? Can you perhaps replace it with an old eternal data (legacy) connection using MSQuery?

 

[stuartmacdonald]

The query just filters a table.

I have queries which filter 5 different tables then I have a combine query into make a single table, which is then read by PowerApps

 

[jkpieterse]

Am I right in understanding that the powerapp is supposed to write to the final table? If not, please explain more, possibly with a schematic drawing.

 

[stuartmacdonald]

Hi Jan

Hope this helps...

Planner.xls is the source
Timesheet.xls uses Power Query to create filtered tables - https://www.dropbox.com/s/fglb8c1azovrykb/PQ eg.png?dl=0
PowerApps references these filtered tables to create dropdown lists - https://www.dropbox.com/s/eknoanb7wcxrkm9/Powerapps example.png?dl=0
The user enters data in PowerApps which is then written back to Timesheet.xls in a different table - https://www.dropbox.com/s/qfrx2thilrwrwtw/Timesheet tab.png?dl=0