Get Current Workbook Path - Excel Power Query

user_7037499 0
Viewed 15714

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

m powerquery excel
3 Answers

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)
  1. Add the following function in PowerQuery. This will return the current directory:
() =>
let
    CurrentDir = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1]
in
    CurrentDir
  1. 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

user_28934080
Related