Power BI Desktop file is in folder with some Excel files. Those Excel files are used as a data source. If we move folder with all of this files to some other location, refreshing of PBID file will not work any more. PBID will not be able to connect to Excel files because fullpath of those Excel files will be changed. Solution is to find address of a folder where PBID file reside. That way, we will always know where our Excel files are.
When we run a PBID, a process with the name PBIDesktop.exe is created. We can find it in Details tab in Task Manager:
There is a console program named Handle64. That program can list all the files that are locked by specific process. In CMD environment we can type:
C:\Users\Sima\Desktop\handle64.exe -p PBID
This will return all files locked by PBIDesktop process:
Because our PBIX file is locked by PBIDDesktop.exe, its fullpath will be listed in there. We just have to filter this list by using name of our PBIX file and we will find location of our PBIX file. That way we will find folders where our Excel files are located, so we can create relative path.
This console program can be downloaded from the address below, but it is also available at the end of this blog post.
All we have to do now is to call this Handle64 program from PBID, and for this we are going to use python script. For python script to work we have to tell Power BI desktop where is pythons home folder. We can do this in Options > Python scripting. If we have python installed we can select its home folder from drop down menu (1). I am using portable version of python so I have to tell PBID explicitly where my python is. I can do that with (2) Browse button.
This is our python script. Blue part will read result of CMD command and encode it in UTF-8. Green part will remove all other text, so that at the end, we only have our fullpath to return.
import pandas import subprocess FilesListBytes = subprocess.check_output(r'"D:\Programi\handle64.exe" -p PBID', shell = True) FilesListString = FilesListBytes.decode('utf-8') FirstSplit = FilesListString.split('Name of the file.pbix', 1) SecondSplit = (FirstSplit.rsplit('File ',1)) ReplaceSlashes = SecondSplit.strip().replace("//","/") df = pandas.DataFrame([[SecondSplit.strip()]],columns=['Files']) print (df)
Name of our PBID file will not be hardcoded. We will read it from table that we prepare in advance in our PBID file.
Sample PBID file, whole Power Query script that contains python code, can be found in this attachment. For this script to work on your computer you have to change fullpath of Handle64.exe. That fullpath is in the first line of the script:
Handle64location = “””C:\Users\Sima\Desktop\handle64.exe”””, change it to where you placed Handle64.exe program.