Power Query Source from Relative Paths

[Magnus Vegem Dahle]

Hi All,

I am making an analysis tool that uses two Excel file sources via Power Query. The tool work well, but as soon as the file and source files are moved to another location it stops working as the Queries sources from aboslute paths.

Can I modify my Sourcing so that as long as the Analysis tool file and the source files are in the same folder the Query will find and use the correct files? So that the Query sources from a path relative to the Analysis Tool file.

Thanks!

Magnus

[#2]

@Rup231470 

Hard to say without the file. If to repeat

Code:

let
WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
FullPathToFile1 = WBPath & "testa.xlsx",
Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true)

in
    Source

Result:

[#3]

@Sergei Baklan 

Maybe this vba code helps:

Function FilePath()
Application.Volatile

FilePath = Application.ActiveWorkbook.Path & "\base.xlsx"

End Function

[#4]

@JeffersonValim 

Actually the question is how to pass that into Power Query, doesn't matter how filepath was generated.

[#5]

@Sergei Baklan, good day. Maybe you will be the right person who can help me. I have tried 2 options to open the list of files in the folder after copying of root folder on One Drive.

On PC in the file I have insert a formula
=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&C4

and named this cell "Path_Directions". It returns

E:\work\crossing forecast\Directions

on PC the next code in PQ works well:

folder = Table.SelectRows(Excel.CurrentWorkbook(), each ([Name] = "Path_Directions")){0}[Content]{0}[Column1],
Source = Folder.Files(folder),

But when I put the folder "Work" on One drive, formula of path have given:
https://fcbc1-my.sharepoint.com/personal/account/Documents/work/crossing forecast/Directions

And PQ stopped. So, I have changed the part of the Source code in two ways:

1st option:
folder = Table.SelectRows(Excel.CurrentWorkbook(), each ([Name] = "Path_Directions")){0}[Content]{0}[Column1],
Source = if Text.Start(folder,4) = "http"
then Web.Contents (folder)
else Folder.Contents (folder),

2nd Option:
Source = SharePoint.Files("https://fcbc1-my.sharepoint.com/personal/account", [ApiVersion = 15])

Both call the window: "We couldn't authenticate with the credentials provided. Please try again."
All levels have Anonymous access but it doesn't help. I cannot avoid this. Please help.

[#6]

@Hanna_Taskaieva 

If I understood correctly you query the files from the folder within the file which is in the same folder. Folder could be on local drive or on OneDrive for Business. If so, such construction shall work

let
    myOneDrive = "https://fcbc1-my.sharepoint.com/personal/account",

    folder = Table.SelectRows(
        Excel.CurrentWorkbook(),
        each ([Name] = "Path_Directions")){0}[Content]{0}[Column1],
    Source =
        if Text.StartsWith(folder, "https")
        then 
            let
                oneDrive = SharePoint.Files(myOneDrive, [ApiVersion = 15]),
                myFolder = Table.SelectRows(oneDrive, each [Folder Path] = folder)
            in myFolder
        else Folder.Files(folder)
in
    Source

First time you run it query asks you for data privacy. You may ignore it or set the same privacy (e.g. Organizational ) for all data sources - current workbook and files in folder.

Above works in my environment, I only used my actual "myOneDrive". 

If the workbook in which you collect all data is in different from source files folder, that is another story.