Power Query Relative Path for OneDrive and Other Users

[ChristopherDuncan]

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:

1. How do I check to see if the file address is a SharePoint link or a valid Path using the above referenced post?
2. 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

[#2]

@Sergei Baklan 

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?

[#3]

@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.