Jul 19 2021 01:29 PM
Jul 19 2021 01:29 PM
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 @Sergei Baklan 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:
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
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.
Jul 19 2021 02:00 PM
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.
Jul 19 2021 02:12 PM
Jul 19 2021 02:34 PMSolution
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.
Jul 20 2021 08:20 AM
@Sergei Baklan Thank You!
For anyone else who comes along, here is what worked for me to get a Power Query Relative Path that works for both file paths and One Drive addresses.
Jul 20 2021 09:02 AM
Great to know it works, thank you for sharing step by step instruction
Jan 06 2022 12:45 PM
Shouldn't the developers just provide a switchable option in Excel to select either absolute or relative?
Jan 06 2022 12:58 PM
Sorry, didn't catch. We speak about file path, correct? Which references do you mean?
Jan 06 2022 03:42 PM
Isn't the problem that Excel always wants to assign absolute addresses (file paths) to queries rather than providing the option of assigning relative paths? Relative paths would allow queries whose data resides in the same folder as the querying workbook to work even if the folder is renamed or moved.
Jan 07 2022 03:24 AM
Power Query itself has no idea with which folder to work and what is current folder. You shall instruct it which folder to take. Such parameter could be taken from CELL("filename"). As soon as you defined folder path, you may use Folder.Files / SharePoint.Files (or Folder.Contents / SharePoint.Contents) connectors to show all files within such folder.
To that folder you may apply filter to select this or that file based on its name (or group of files based on subfolder or something else). Actually that will be imitation of relative part.
But in any case you have to start from defined as parameter root folder navigating within it and ignoring absolute path.
Such approach could affect performance, especially with SharePoint folder.
Jan 07 2022 12:47 PM
Apr 28 2022 11:15 AM
What if I am trying to reference a folder and not a file (as in excel workbook). Will I still use Web.Contents & File.Contents?
Apr 28 2022 02:28 PM
I don't really understand what you mean. Please can you show me the code syntax for it?