Excel, OneDrive and Sharepoint relative path

[Anonymous]

I am using OneDrive and Excel as part of Office 365. In the link below, Ken Puhls writes about using the Cell function to get the path of the open excel file to determine where to get other files (a relative path).

https://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/ 

I use this technique and it works great. However, sometimes my team members open the file directly from the sharepoint site and the link returned is 

In theory, I could detect the value (https) and then pull the files from the relative path off of the Sharepoint site. Has anyone tried this approach? I would imagine that the queries could be much slower as they would be working over the network as opposed to the local OneDrive. 

Thank you,

Mike

[KNP]

Hi Mike,

I have had similar issues. I chose to get around it by using VBA and mapping the sharepoint site as a network drive prior to any query execution but that may not be an option for you. 

I think your idea about checking the path before (or not before - see link below) executing the code should work relatively easily. 

If you had both blocks of code in the same query, one for sharepoint and one for local path you could then just execute conditionally based on the path. 

This is the best post I've seen about conditional code branching in Power Query (https://community.powerbi.com/t5/Community-Blog/Conditional-Code-Branching-in-Power-BI-Query-if-then...), may be useful.

I don't have time to test this right now but I'm curious to hear your results and whether the performance is significantly different.

Regards,

Kim

[Jimmy801]

Hello @Anonymous 

I worked a lot with both techniques (using reading relative path of file as well as reading from the sharepoint). I've encountered never a problem with both of them. However I never tried to read all files from a relative path of the sharepoint. Are you using sharepoint online? But I think there should be no problem using this technique.

Jimmy

[Anonymous]

Hi @Jimmy801 ,

I am using Sharepoint online. I tried writing code that would dynamically handle either situation about a year ago. I never did get it right and so I built everying using OneDrive. I was wondering if anyone else attempted this approach.

Thank you,

Mike