Get Current Workbook Path - Excel Power Query

Question

Trying to add a custom column and populating the value with the current workbook path name.

I have tried Excel.Workbook.name and Excel.CurrentWorkbook() and other objects, but it seems those are limited to pulling data.

in VBA this is simply WorkbookObject Path property. but with power query its another story. The references and libraries on Microsoft site are limited for power query.

https://msdn.microsoft.com/en-us/library/mt779182.aspx

Solution 1

There is no direct way to do this in Power Query. If you can fill the value into a cell you can get that value through Excel.CurrentWorkbook.

Solution 2

Instead of using VBA, you can use the following method which merely involves using an Excel formula:

1. Define the following formula in Excel and name this cell "FilePath":

=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)

2. Add the following function in PowerQuery. This will return the current directory:

() =>
let
    CurrentDir = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1]
in
    CurrentDir 

3. Now you can import your CSV (or other) file from the current directory:

let
    Source = Csv.Document(File.Contents(currentdir() & "filename.csv"),[Delimiter=";", Columns=15, Encoding=65001, QuoteStyle=QuoteStyle.None])
in
    Source

Credits: https://techcommunity.microsoft.com/t5/excel/power-query-source-from-relative-paths/m-p/206150

Solution 3

You can use VBA and have a cell filled in when the file is opened during the Workbook_Open event:

Private Sub Workbook_Open()
Dim root As String

root = ActiveWorkbook.path

Range("root").Value = root
'root is the named range used in power query.
End Sub

You can then get this variable from the named range ("root") into Power Query by doing something along these lines:

let
    Source = Excel.CurrentWorkbook(){[Name="root"]}[Content][Column1]{0}
in
    Source

Comments

[dfresh22]

Yea I figured as much. not the most elegant but with some post processing I do the following. ListObject.DataBodyRange.Columns(3).Formula = "=""" & WorkbookObject.FullName & """"