Forum Discussion
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-from-relative-paths/m-p/206150) which was very helpful. I've also read through other posts by SergeiBaklan which were helpful identifying my issues. The problem is that some of my users may be using OneDrive, others OneDrive for Business, and still others not using OneDrive at all.
When I follow the directions in the above quoted link and then send the file to someone using OneDrive, they cannot refresh the Query. The error message says there is a Data Format Error and that the supplied file path is invalid because it is a hyperlink. Which leads me to two questions:
- How do I check to see if the file address is a SharePoint link or a valid Path using the above referenced post?
- How do I convert a SharePoint address to the valid path that Excel wants?
I would imagine it would be some form of an If then statement:
If filepath is a Sharepoint link
Use this address for the file
Else
Follow the directions in the above post to get the filepath.
I'm just not familiar enough with the language to even begin to parse that out.
Any help would be greatly appreciated.
Thanks!
Christopher
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.
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.
- 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?
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.
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
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
Don't use Dropbox, thus can't test. Perhaps From Web connector with URL on the file could work. One case is here Connecting Dropbox Excel files to Power BI - Microsoft Power BI Community
