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