May 22 2020 09:47 AM
I often have to change the actual path of my workbook, or share it, so I want to change the query location in a way that as long as both files are in the same directory, they work
Source = Excel.Workbook(File.Contents("C:\Users\me\Documents\file.xlsx"), null, true),
I want that as long as file.xlsx is in the same directory as my workbook, it will find it
Source = Excel.Workbook(File.Contents("\file.xlsx"), null, true),
Ive try this but does not work
May 22 2020 10:05 AM
Please check this thread https://techcommunity.microsoft.com/t5/excel/power-query-source-from-relative-paths/m-p/206150
May 22 2020 01:33 PM
I was trying that but I get an error
let
WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
FullPathToFile1 = Table1 "filename.xlsx",
Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true),
Expression.SyntaxError: Token Comma expected.
May 22 2020 01:59 PM
I didn't catch what the Table 1 is in your script. Let me illustrate on my sample.
Here
we have the cell named filepathCELL which returns full path of the current file. With that entire script could be
let
myAnotherFile = "dataFile.xlsx",
thisFilePath = Excel.CurrentWorkbook(){[Name="filepathCELL"]}[Content]{0}[Column1],
onlyPath= Text.Split(thisFilePath,"["){0},
fullpathAnotherFile = onlyPath & myAnotherFile,
Source = Excel.Workbook(File.Contents(fullpathAnotherFile))
in
Source
Please note that works only on local folders, with folders synced with Onedrive/Sharepoint CELL() returns web-like file path (http://...)
May 22 2020 02:06 PM
I added the Table1 trying to figure what to do
WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
FullPathToFile1 = WBPath & "Filename.xlsx",
Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true),
If I try this one, I get
Expression.Error: We couldn't find an Excel table named 'WBPath'.
Details:
WBPath
May 22 2020 02:10 PM
yes, here shall be Table1 in your case
Excel.CurrentWorkbook(){[Name="Table1"]}[Content]{0}[Column1]
May 22 2020 03:04 PM
Expression.Error: The field 'Column1' of the record wasn't found.
May 22 2020 03:08 PM
Ive try modifying it but then I get this error later
Expression.Error: We cannot apply operator & to types Record and Text.
Details:
Operator=&
Left=[Record]
Right=Filename.xlsx
May 23 2020 08:31 AM - edited May 23 2020 08:35 AM
I'd recommend you to do things step by step for better understanding how Power Query works.
1) Create in Excel sheet the Table as here
You may use any table and column names you prefer, here they are tblFilePath and FilePath accordingly.
2) Query it - stay on Table and on ribbon Data->From Table/Range
3) If you didn't change default options, most probably you will see two generated steps, Source and Changed Type. Let keep them, result is
3) We need extract text with filepath now, that's first element in FilePath column. Since numbering in Power Query start from zero, let add in formula bar to above [FilePath]{0} and Enter. Result will be
and Power Query shows two steps
We still have two steps adding Navigation to second one. It returns the text with filepath.
4) Now we need to extract from it only current folder name. That will be the text before [ , in our case c:\test\. Thus we may split our text on two parts - before [ and after it, and take first part only. First let click on Fx near formula bar
In formula bar the name of the previous step is shown, let wrap it by formula and Enter
5) As result we have a list with two parts of filpath text. We need first element of the list. Remembering that numbering starts from zero, add {0} to formula and Enter
6) To combine with target filename (let say myTarget.xlsx) to generate its full path in current directory, again Fx
and in formula bar add the text
Result will be in Custom2
and we may use it for accessing Custom2 file in same folder
If open Advanced Editor, generated code will be
let
Source = Excel.CurrentWorkbook(){[Name="tblFilePath"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"FilePath", type text}})[FilePath]{0},
Custom1 = Text.Split(#"Changed Type", "["){0},
Custom2 = Custom1 & "myTarget.xlsx"
in
Custom2
We may create function from it, or at least to make bit more compact and readable - we don't need type changing here and better to have step names which say us what we do
let
Source = Excel.CurrentWorkbook(){[Name="tblFilePath"]}[Content][FilePath]{0},
currentFolderPath = Text.Split(Source, "["){0},
targetFilepath = currentFolderPath & "myTarget.xlsx"
in
targetFilepath
May 26 2020 10:37 AM
I get error
Expression.Error: The column 'FilePath' of the table wasn't found.
Details:
FilePath
May 27 2020 12:28 AM
That only means this column in the table has another name.
May 27 2020 05:06 AM
May 27 2020 10:30 AM
@Ocasio27 Entire specific table doesn't return the value, even if it is only one cell with data in such table. To get such value you shall reference on the table first, after that on column where the values is (doesn't matter it's only one column), after that on first element in this column (doesn't matter it's only one element in it).
May 27 2020 01:08 PM
Ive try to substitute the value with the first column name, which is "RECORD" and what it returns in the value of the first row in first column and the name of the file filename.xlsx
May 28 2020 07:36 AM
When it's most probably
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][RECORD]{0}
May 29 2020 06:31 AM
I am still not getting the results expected. Could you please post 2 excel files linked? one with 2 different tables the other with the queries.
May 29 2020 02:09 PM
Ok I managed to fix it, now the only remaining problem is that it requires a cell =CELL("filename") which is something I cannot do with PowerBI
May 29 2020 02:47 PM
=CELL("filename") is within Excel sheet, that's not Power Query function. You query result from the range/table in the sheet.
Jan 30 2021 08:01 PM
Jan 31 2021 06:39 AM
That's the same, take part of URL till last slash, add filename and you'll have path fot another file in same folder. Query it as
= Excel.Workbook(Web.Contents("https://MyPath/NewFile.xlsx"), null, true)