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

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]

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

[#3]

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?

[#4 · Accepted solution]

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

[#5]

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

1. Created a Worksheet titled Setup with this formula in cell B4:
   =LEFT(CELL("filename",A1), FIND("[", CELL("filename",A1))-1)
2. Created a Defined Name for B4 and called it FilePath
3. Hid the Setup Worksheet
4. Clicked in my previously linked data I imported with Power Query.
5. Clicked the Query tab in the Ribbon
6. Clicked Edit on the Query tab
7. Clicked Advanced Editor on the Home tab of Power Query Editor
8. Changed the source line of my code to read as follows:
   FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
   FullPathToFile1 = FilePath & "export-information\orders.xlsx",
   Source = if Text.Start(FullPathToFile1, 4) = "http"
   then Excel.Workbook(Web.Contents(FullPathToFile1), null, true)
   else Excel.Workbook(File.Contents(FullPathToFile1), null, true),

[#6]

@ChristopherDuncan 

Great to know it works, thank you for sharing step by step instruction

[#7]

@SergeiBaklan 

Shouldn't the developers just provide a switchable option in Excel to select either absolute or relative?

[#8]

@G_Mack 

Sorry, didn't catch. We speak about file path, correct? Which references do you mean?

[#9]

@SergeiBaklan 

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.

[#10]

@G_Mack 

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.

[#11]

IMO, the developers could make it much easier for unsophisticated users like me by providing the option of relative query paths rather than absolute paths. I typically put the workbook and query data in the same folder and use simply use Excel to "Browse" to it. Excel then provides the correct absolute path to the data, So, if that folder is ever renamed, moved, or shared the query paths are no longer valid.

[#12]

@ChristopherDuncan @sergei_baklan

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?

[#13]

@johnnydevv 

No, that's From (Sharepoint) Folder connector

[#14]

@SergeiBaklan 

I don't really understand what you mean. Please can you show me the code syntax for it?

[#15]

@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

[#16]

@AlexCardoso 

I guess it shall be 4 here

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

[#17]

@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

[#18]

@Louis-Edouard 

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

[#19]

@ChristopherDuncan 

I am trying to pull in files from my company sharepoint environment and have followed the code to the T

Here it is

let

FilePath=Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],

FullPathToFile1 = FilePath & "Outline_WAG_WITHOUT_CONNECTION.xlsx",

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

then Excel.Workbook(Web.Contents(FullPathToFile1),null,true)

else Excel.Workbook(File.Contents(FullPathToFile1),null,true),

But I can't seem to pull the data in. Is there an way you can still help with this?

Best,

Neha

[#20]

@NG_CAX 

It depends on how did you defined named cell FilePath within the grid. For example, that could be

=TEXTBEFORE( CELL("filename"), "/",-1) & "/"