Forum Discussion

ChristopherDuncan's avatar
ChristopherDuncan
Copper Contributor
Jul 19, 2021
Solved

Power Query Relative Path for OneDrive and Other Users

Let me start by saying that I have read the posts about converting from an Absolute path to a Relative path in PowerQuery (primarily https://techcommunity.microsoft.com/t5/excel/power-query-source-fr...
BI & Data Analysis
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 19, 2021

    ChristopherDuncan 

    That could be like

    Source = if Text.Start(filepath,5) = "https"
         then  Web.Contents(...)
         else  Csv.Document(...),

    but you shall to clean filepath before (remove sheet name and brackets). You may do in sheet by formula or in Power Query by another if then else. Hope it's possible to find ready to use pattern for both.

SergeiBaklan's avatar
SergeiBaklan
MVP
Jul 19, 2021

ChristopherDuncan 

That's only parse CELL("filename"). If it starts from "https://..." remove sheet name from the end, substitute [] around filename on nothing and connect like

Source = Excel.Workbook(Web.Contents("https://tenant.sharepoint.com/teams/site/docs/file.xlsx"), null, true),
...

 If not, use connection as in mentioned by you thread.

  • Louis-Edouard's avatar
    Louis-Edouard
    Copper Contributor
    Apr 07, 2023

    SergeiBaklanor any other...

    Hello everybody, not an expert on Excel or Query, I have similar problems and can't find any solutions.

    All files (sources and workings files) are on Dropbox with local copy on hard drive. Dropbox folder is on a regular path as: C:\Users\"username"\Dropbox

    I tried the online path of the file but it doesn't work, which is, I presume, what has suggested Sergei. I can't find how I ask PowerQuery to change the path (username) according to the connected user of the file.

    (Also, sorry for my poor English...)

    Thank you for your help

     

  • AlexCardoso's avatar
    AlexCardoso
    Copper Contributor
    Sep 07, 2022

    SergeiBaklan 

    Hi Sergei,

    I'm trying to stage the files i need in sharepoint or any other place where the file path is a URL as well but even though i have changed the power query code to:
    Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1],
    FullPathToFile1 = Filepath & "file.csv",
    Source = if Text.Start(FullPathToFile1, 6) = "http"
    then Csv.Document(Web.Contents(FullPathToFile1),[Delimiter=",", Columns=23, Encoding=65001, QuoteStyle=QuoteStyle.None])
    else Csv.Document(File.Contents(FullPathToFile1),[Delimiter=",", Columns=12, Encoding=65001, QuoteStyle=QuoteStyle.None]),

     

    I still get the DataFormat.Error: The supplied file path must be a valid absolute path.

    Would appreciate some help on this one.

     

    Thanks

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Sep 09, 2022

      AlexCardoso 

      I guess it shall be 4 here

      Source = if Text.Start(FullPathToFile1, 4) = "http"

  • ChristopherDuncan's avatar
    ChristopherDuncan
    Copper Contributor
    Jul 19, 2021
    Thanks Sergei, but you’ve found my problem. How do I write the code in power query that will switch from one to the other? Or should I do that in Excel?
    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jul 19, 2021

      ChristopherDuncan 

      That could be like

      Source = if Text.Start(filepath,5) = "https"
         then  Web.Contents(...)
         else  Csv.Document(...),

      but you shall to clean filepath before (remove sheet name and brackets). You may do in sheet by formula or in Power Query by another if then else. Hope it's possible to find ready to use pattern for both.

Resources