Jun 20 2018 06:16 AM - edited Jun 20 2018 06:17 AM
Hi All,
I am making an analysis tool that uses two Excel file sources via Power Query. The tool work well, but as soon as the file and source files are moved to another location it stops working as the Queries sources from aboslute paths.
Can I modify my Sourcing so that as long as the Analysis tool file and the source files are in the same folder the Query will find and use the correct files? So that the Query sources from a path relative to the Analysis Tool file.
Thanks!
Magnus
Jan 09 2023 11:09 AM
Hard to say without the file. If to repeat
Code:
let
WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
FullPathToFile1 = WBPath & "testa.xlsx",
Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true)
in
Source
Result:
Feb 02 2023 08:54 AM
Maybe this vba code helps:
Function FilePath()
Application.Volatile
FilePath = Application.ActiveWorkbook.Path & "\base.xlsx"
End Function
Feb 13 2023 08:00 AM
Actually the question is how to pass that into Power Query, doesn't matter how filepath was generated.
May 04 2023 12:36 PM
May 08 2023 05:19 AM
If I understood correctly you query the files from the folder within the file which is in the same folder. Folder could be on local drive or on OneDrive for Business. If so, such construction shall work
let
myOneDrive = "https://fcbc1-my.sharepoint.com/personal/account",
folder = Table.SelectRows(
Excel.CurrentWorkbook(),
each ([Name] = "Path_Directions")){0}[Content]{0}[Column1],
Source =
if Text.StartsWith(folder, "https")
then
let
oneDrive = SharePoint.Files(myOneDrive, [ApiVersion = 15]),
myFolder = Table.SelectRows(oneDrive, each [Folder Path] = folder)
in myFolder
else Folder.Files(folder)
in
Source
First time you run it query asks you for data privacy. You may ignore it or set the same privacy (e.g. Organizational ) for all data sources - current workbook and files in folder.
Above works in my environment, I only used my actual "myOneDrive".
If the workbook in which you collect all data is in different from source files folder, that is another story.