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.
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?
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.
- Thank you very much.
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.
- Created a Worksheet titled Setup with this formula in cell B4:
=LEFT(CELL("filename",A1), FIND("[", CELL("filename",A1))-1) - Created a Defined Name for B4 and called it FilePath
- Hid the Setup Worksheet
- Clicked in my previously linked data I imported with Power Query.
- Clicked the Query tab in the Ribbon
- Clicked Edit on the Query tab
- Clicked Advanced Editor on the Home tab of Power Query Editor
- 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),
I am trying to pull in files from my company sharepoint environment and have followed the code to the T
Here it is
letFilePath=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,NehaIt depends on how did you defined named cell FilePath within the grid. For example, that could be
=TEXTBEFORE( CELL("filename"), "/",-1) & "/"
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?
No, that's From (Sharepoint) Folder connector
Great to know it works, thank you for sharing step by step instruction
Shouldn't the developers just provide a switchable option in Excel to select either absolute or relative?
- Created a Worksheet titled Setup with this formula in cell B4:
