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]

You cannot have a powerapp write to timesheet.xls and then expect planner.xls to be updated. Powerquery is (as far as I know) a one-way street.

 

[stuartmacdonald]

Not expecting to write back to planner.xls, however, your right, the crux of my problem is that the table cannot have multiple connections, so when the workbook is connected to PowerApps, the table no longer updates...

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

What I need is another way to create my filtered tables e.g. VBA, but not sure where to start.

Or could I create a mirrored copy of the workbook using VBA and connect PowerApps to this copy?

Or could I use macros to force the tables to update? If I right click and press 'load' I can redraw the table, remove the original and rename it....

 

[offthelip]

What about using the standard excel method of linking workbooks just with a link? the code below is just a demonstration of how you can automatically link workbooks together using VBA. This will eliminate the powerquery for the updates.
Put the code into a blank workbook and run it. It will set up Sheet1 as a template with the data determining what links are put where. It will then generate a new workbook and create links back to sheet 2 of the original workbook.

Sub test()
' set up data this is not part of the real code it is just to set up the template for some links
 Worksheets("Sheet1").Select
 For i = 2 To 20
  Cells(3, i) = "B" & i
 Next
 
 '' the real code starts here
addlinks = Sheets("Sheet1").Range("A3:Z3").Value
CurrentWorkbookName = ActiveWorkbook.Name


 Workbooks.Add
 NewWorkbookName = ActiveWorkbook.Name
 newsheetname = ActiveSheet.Name
' set rown to whatever the next empty row is
Rown = 10


    Application.ScreenUpdating = False
      For jj = 1 To 26
        If addlinks(1, jj) <> "" Then
            Windows(NewWorkbookName).Activate
            Worksheets(newsheetname).Select
            
            
            linkad = addlinks(1, jj)
            Range(linkad).Select
            Selection.Copy
            
             Windows(CurrentWorkbookName).Activate
             Worksheets("sheet2").Select
            
             Cells(Rown, jj).Select
             ActiveSheet.Paste Link:=True
        End If
       Next jj
'       ThisWorkbook.Save
       
      Application.ScreenUpdating = True
' this bit of code is not part of the real code it is just to put some values inthe  linked cells




            Windows(NewWorkbookName).Activate
            Worksheets(newsheetname).Select
For j = 1 To 15
 Cells(j, 2) = "Row" & j
Next j


             Windows(CurrentWorkbookName).Activate
             Worksheets("sheet2").Select


End Sub

Note I have used this method and created links to over 1000 workbooks on a company network all in different folders from 1 master workbook and it all worked perfectly. It has the advantage that when the "child" workbooks are updated EXCEL automatically updates the master. sometimes it takes a while it certainly updates when you reopen the master.