Forum Discussion

NG_CAX's avatar
NG_CAX
Copper Contributor
Jun 02, 2023

Re: Power Query Relative Path for OneDrive and Other Users

ChristopherDuncan 

 

I am trying to pull in files from my company sharepoint environment and have followed the code to the T

 

Here it is

 

let
FilePath=Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
FullPathToFile1 = FilePath & "Outline_WAG_WITHOUT_CONNECTION.xlsx",
Source = if Text.Start(FullPathToFile1,4) = "http"
then Excel.Workbook(Web.Contents(FullPathToFile1),null,true)
else Excel.Workbook(File.Contents(FullPathToFile1),null,true),
 
But I can't seem to pull the data in. Is there an way you can still help with this?
 
Best,
Neha

 

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 02, 2023

    NG_CAX 

    It depends on how did you defined named cell FilePath within the grid. For example, that could be

    =TEXTBEFORE( CELL("filename"), "/",-1) & "/"
    • WCooke42's avatar
      WCooke42
      Copper Contributor
      Sep 01, 2023

      SergeiBaklan 

       

      I used the CELL("filename",$A$1) function above to obtain my file path.  This returned a relative path with forward slashes.  Power Query error said that it needs to be an absolute path, so I replaced the forward slashes with back slashes.  I am trying to grab my data from a folder with multiple files and the source function keeps failing with the following code:

       

      let

      FilePath = Excel.CurrentWorkbook(){[Name="MyFilePath"]}[Content]{0}[Column1],

      FullPathSales = FilePath & "Data Files\Sale Detail",

      Source = Folder.Files(FullPathSales), ((folder path must be valid absolute path))

       

      I have tried some of the other functions for the Source, 

      Excel.Workbook(Web.Contents(FullPathSales), null, true) ((Format Error: Host Name cannot Parse)) & Excel.Workbook(File.Contents(FullPathSales), null, true) ((Must be valid absolute path)). 

       

      The FullPathSales result is:

      https:\\.....my.sharepoint.com\.....\Documents\Reports\Sales Metrics\Tasting Room\Data Files\Sale Detail\

       

      copy/paste the link in my browser works fine, both forward slash and back slash addresses. 

       

      What am I doing wrong?

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 01, 2023

        WCooke42 

        Perhaps you have other names. Please try

        let
    Source = Table.SelectRows(Excel.CurrentWorkbook(), each ([Name] = "FilePath"))[Content]{0}[Column1]{0},
    FullPathSales = Source & "test/testflow 01.xlsx",
    getSales = Excel.Workbook(Web.Contents(FullPathSales), null, true)
in
    getSales

        assuming we are in root, and change subfolder/filename on your actual ones.

         

Resources