Hi everyone,
I am trying to create a relative path to perform a combine & load for multiple Excel files in a folder. I've been able to create relative paths for a single workbook, but I can't get the functionality the same for a relative path to a folder. Below is what I have:
I have a "GetValue" function;
(rangeName) =>
Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]
Which I use in the query below to import a specific file using a relative path;
let
Source = Excel.Workbook(File.Contents(GetValue("MyExcelFileDataPath"))),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
where "MyExcelFileDataPath" is a named cell which contains the full data path.
But I want to use a similar relative path, instead of the static path that is present in my combine & load query;
let
Source = Folder.Files("C:Usersabc123ReportsReports Are Located Here"),
I've tried using the GetValue function and another named cell with the full data path (something like the below), but it doesn't work;
let
Source = Folder.Files(GetValue("Data path to report location"))
Power Query doesn't seem to recognize the use of GetValue to name the relative path for my folder files. Is there a way to use a relative path specifically for combining & loading a folder?
Thanks!
Hi,
I normally store my file/folder path in a single column Excel table that contains one row. The Table is called "PathTable" and the column is called "FolderPathColumn". The single cell in the PathTable contains a CELL function to automatically return the file path of the query file.
I save my query file in the folder above the folder containing the data you want to get. This folder is called 'SourceFiles'. I therefore need to use the Text.Combine function to append the folder name 'SourceFiles' to the file path generated by the CELL function, as shown below:
let
//Get Text or CSV files from a folder
Path = Excel.CurrentWorkbook(){[Name="PathTable"]}[Content],
Source = Folder.Files(Text.Combine({Path[FolderPathColumn]{0} ,"SourceFiles"},""))
in
Source
Mynda
Thanks Mynda,
I used your "PathTable" method and was able to get it work, however I had to use a search function inside a left function to trim out the file name and sheet name to retrieve only the folder path.
=LEFT(CELL("filename"),SEARCH("[",CELL("filename"))-1)
