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

Answer 1

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

Answer 2

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.