Forum Discussion
Solved
Power Query Relative Path for OneDrive and Other Users
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-fr...
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.
Shouldn't the developers just provide a switchable option in Excel to select either absolute or relative?
Sorry, didn't catch. We speak about file path, correct? Which references do you mean?
- 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.
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.
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.