Forum Discussion

Magnus Vegem Dahle's avatar
Magnus Vegem Dahle
Brass Contributor
Jun 20, 2018
Solved

Power Query Source from Relative Paths

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 ...
excel
Formulas and Functions
Get & Transform
Power Query
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 20, 2018

    Hi Magnus,

     

    You may find the current file path putting in any cell of your workbook the formula

    =LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)

    name this cell somehow, let say "FilePath" and generate absolute path in query like

    <prev step>
    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    FullPathToFile1 = FilePath & "Name1.xlsx"
<next step>

     

Hanna_Taskaieva's avatar
Hanna_Taskaieva
Copper Contributor
May 04, 2023
SergeiBaklan, 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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 08, 2023

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.

Resources