Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi there,
I have a file with multiple queries via Power Query and tables via Power Pivot Data Model that are calculated one after another as shown below. The final result cannot be reflected correctly in the final table (Table 3) by using Refresh All to run as it will trigger all queries and tables refresh at the some time, therefore calculation cannot be flushed through without clicking Refresh All button a few times.
I need to run them in an order as indicated in the Yellow from 1 to 5. I have disabled Background Refresh (Right click >> Properties) but it does not work. I have seen some others actually use VBA to execute the refresh in steps (https://stackoverflow.com/questions/69558829/vba-refresh-power-query-and-subsequently-update-pivot-t...) and may I know how do I write the code for my scenario below?
Update:
The following code works for running specific Data Model
ThisWorkbook.Model.ModelTables("DataModelName").Refresh
The following codes do not work for running specific Query and I keep getting error as Run-time error "9" Subscript out of range
SUB Macro_Name()
ThisWorkbook.Connections("QueryName").Refresh
End Sub
ThisWorkbook.Worksheets("ExcelSheetName").ListObjects("QueryName").QueryTable.refresh BackgroundQuery:=False
ThisWorkbook.Worksheets("ExcelSheetName").ListObjects("QueryName").QueryTable.Refresh
Thank you very much!!
Regards,
M
Update to my own question above,
I have found using a wrong variable name and that's what Error '9' is telling me. You may use the following code to run the list all of your connections/queries and I simply missed we need to add "Query - " in front of your query name, i.e. query name as QueryReport then it should be "Query - QueryReport". Hope this helps anyone with the same problem, thank you
Sub ListAllQueriesandConnection()
Dim conn As WorkbookConnection
For Each conn In ActiveWorkbook.Connections
Debug.Print conn.Name
Next
End Sub
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |