PQ execute queries asynchronously

Power Query / By / July 27, 2021

Power Query is great tool for preparing data to be written into CSV file. CSV files are usually used to import data into databases but the requirement is that they must be formatted in specific way. We can export formatted data to a spreadsheet, and then we can use VBA to write already formatted data to a CSV file.

Data connections in Power Query are by default set to be asynchronous. If we start a query with "Workbook.RefreshAll" from VBA procedure, the rest of VBA procedure won't wait for the query to complete. Instead of writing new data from the spreadsheet to a CSV file, VBA procedure will write the old ones, because there will be no time for the query to complete.

The solution is to change the execution of the query to synchronous. This is done in Excel Connection Properties. We can find our connection properties in Data tab (1) > Workbook Connections Properties (2). There is a checkbox "Enable background refresh". This checkbox should be disabled.

The checkbox can be disabled directly from the VBA. That possibility allow us to fully control the execution of a query in procedure. This procedure will temporarily change BackgroundQuery properties to False so that our Procedure will wait for the query to complete.

Sub Refresh_All_Data_Connections()
    For Each objConnection In ThisWorkbook.Connections
        'Get current background-refresh value
        bBackground = objConnection.OLEDBConnection.BackgroundQuery
        'Temporarily disable background-refresh
        objConnection.OLEDBConnection.BackgroundQuery = False
        'Refresh this connection
        objConnection.Refresh
        'Set background-refresh value back to original value
        objConnection.OLEDBConnection.BackgroundQuery = bBackground
    Next
End Sub

Excel sample table:

Download

Connection to The Desired PBID file

Power BI Desktop / By / August 1, 2021

Connecting to a Power BI desktop is easy if we have credentials available. It's not hard to read credentials. If more than one Power BI desktop file is open, the question is how to connect to the desired file.

Each time a PBIX file is opened, new credentials are written to the default location. That default location is usually:
C:\Users\<Username>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces
If we have more files open, more credentials will be written. In the image below we see that three PBIX files are open.

Each of these folders contains a Data subfolder. Within that subfolder are two files that contain the address and name of the database we want to link to. The database name is in the XML file name (looks like "c6fb254f-f146-464d-99cf-b2a6e322eb38.1.db.xml"), and the database address is the number found in the msmdsrv.port.txt file (looks like "58139"). After closing the file, its credentials folder will be deleted.

Each time a PBIX file is opened, its database will have different credentials. It is not easy to make a direct link between the file name and its credentials. The problem is that we don't know which of the credentials to use to link to the desired file. We can solve this problem by creating a table that contains the PBID file name in each PBID file. If we know all the credentials, we can read file names from all tables. When we find the name we want, we will know which credentials to use.

We can now use Power Query to get the necessary data. We will first read the credentials for all three PBIX files.

Next, we will read the names of the open PBIX files and filter only the desired file. That will tell us which credentials to use.

Here are three PBID sample files and one Excel sample file:
Download