Forum Discussion
Power Query Relative Path for OneDrive and Other Users
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.
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
It depends on how did you defined named cell FilePath within the grid. For example, that could be
=TEXTBEFORE( CELL("filename"), "/",-1) & "/"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?
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 getSalesassuming we are in root, and change subfolder/filename on your actual ones.
