load data into power BI from relative path

Question

I am trying to find a solution to load an external data file but from a relative path, so when someone else open my PBIX it will still work on his/her computer.

many thanks.

Answer 1

Relative paths are *not* currently supported by Power BI.

---

To ease the pain, you can create a variable that contains the path where the files are located, and use that variable to determine the path of each table. That way, you only have to change a single place (that variable) and all the tables will automatically point to the new location.

Create a Blank Query, give it a name (e.g. dataFolderPath) and type in the path where your files are (e.g. C:\Users\augustoproiete\Desktop)

With the variable created, edit each of your tables in the Advanced Editor and concatenate your variable with the name of the file.

e.g. instead of "C:\Users\augustoproiete\Desktop\data.xlsx", change it to dataFolderPath & "\data.xlsx"

---

You can also vote/watch this feature request to be notified when it gets implemented:

• Support relative path to excel/csv sources

Answer 2

You can use also the "Parameters" function. 1. Create a new Parameter like "PathExcelFiles" Parameter_ScreenShot

2. Edit your "Source" entry SourceEntry_ScreenShot

Done !

Answer 3

I don't think this is possible yet.

Please add your support for this idea so the Microsoft Power BI team will be more likely to add this as a new feature.

Answer 4

I couldn't bear the fact that there is no possibility to use relative paths, but finally I had to...

So I tried to find a half-decent acceptable workaround. Using Python-Script it is at least possible to get access to the users %HOME% directory.

let
    PySource = Python.Execute("from pathlib import Path#(lf)import pandas as pd#(lf)dataset = pd.DataFrame([[str(Path.home())]], columns = [1])"),
    homeDir = Text.Trim(Lines.ToText(PySource{[Name="dataset"]}[Value][1])),
    ...

The same should be possible with R-Script but didn't do it.

Anybody knows any better solution to get the %HOME% directory inside "Power" Query? I would be glad to have one.

Then I created two scripts inside my working directory install.bat:

@ECHO OFF
if exist "%HOME%\.pbiTemplatePath\filepath.txt" GOTO :ERROR

#This is are the key commands
mkdir "%HOME%\.pbiTemplatePath"
echo|set /p="%cd%" > "%HOME%\.pbiTemplatePath\filepath.txt"

GOTO :END

#Just a little message box
:ERROR
SET msgboxTitle=There is already another working directory installed.
SET /p msgboxBody=<"%HOME%\.pbiTemplatePath\filepath.txt"
SET tmpmsgbox=%temp%\~tmpmsgbox.vbs
IF EXIST "%tmpmsgbox%" DEL /F /Q "%tmpmsgbox%"
ECHO msgbox "%msgboxBody%",0,"%msgboxTitle%">"%tmpmsgbox%"
WSCRIPT "%tmpmsgbox%"
:END

and uninstall_all.bat:

@ECHO OFF
if exist "%HOME%\.pbiTemplatePath\filepath.txt" RMDIR /S /Q "%HOME%\.pbiTemplatePath\"

So in "Power" BI I did this:

let
    PySource = Python.Execute("from pathlib import Path#(lf)import pandas as pd#(lf)dataset = pd.DataFrame([[str(Path.home())]], columns = [1])"),
    homeDir = Text.Trim(Lines.ToText(PySource{[Name="dataset"]}[Value][1])),
    workingDirFile = Text.Combine({homeDir, ".PbiTemplatePath\filepath.txt"} , "\"),
    workingDir = Text.Trim(Lines.ToText(Csv.Document(File.Contents(workingDirFile),[Delimiter=";", Columns=1, QuoteStyle=QuoteStyle.None])[Column1])),
    ...

Now if my git-repository (containing a "Power" BI-template-file and some config-files saying the template where to load the data from and the install/uninstall-scripts). Install has to be executed once and nobody has to copy and paste any path.

I'd be glad about any suggestion of improvement. It's not the solution Gotham deserves... Gotham deserves a better one.

Answer 5

As mentioned by a few people, you can use a dataset parameter and reference that in your script. What I haven't seen mentioned is that you can change these values using an API call: https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-parameters

Answer 6

Well, I kind of found a way to do that.

In my case I just want to change the Windows User name in the path of a file

let
    MainFolder = "C:\\Users\\",
    FolderData = "\\Downloads\\",
    File = "teste.xlsx",
    SheetName = "Plan1",
    Users = {"Mateo.Tesla", "Carl.Smith", "Bruna.Dias", "John.Mayer"}, // Add Here the different People will access your data
    Paths = List.Transform(Users, each Text.Combine({MainFolder, _, FolderCBERS, File}, "")),
    Lists = try List.RemoveNulls(List.Transform(Paths, each try Excel.Workbook(File.Contents(_), null, true) otherwise null)) otherwise null,
    TheTable = Lists{0},
    Sales_Sheet = TheTable{[Item=SheetName ,Kind="Sheet"]}[Data]
in
    Sales_Sheet