在 Excel Power Query 和 PowerBI 使用動態路徑

 

使用 Excel Power Query 或 Power BI 一段時間後，發現到一件很惱人的事情： 

當初的資料來源是本機檔案，例如在某個目錄之下的 excel 檔、log 檔、csv 檔、txt 檔 ... 在搬動或重新命名整個資料夾、複制到隨身碟、或與他人共享查詢時，重新整理資料就會找不到資料來源!! 

 這是因為它們的來源數據是以「絕對路徑」的方式來連結，為了解決這個問題就必須手動修改來源路徑。 有沒有什麼方法可以"動態"取得路徑??

1.在 Excel Power Query 使用動態路徑

利用函數 cell 取得目前檔案的路徑 : 

一開啟檔案就會自動更新公式以取得目前檔案路徑，從而達到動態路徑的效果

第 1 步–

以公式來檢索當前檔案路徑。當檔案目錄位置改變時，這個公式會自動更新

=LEFT(CELL("filename"),FIND("[",CELL("filename"))-2)

第 2 步–

接下來就是怎麼把這一儲存格的內容導入給 Power Query 使用了

有二種方式, 有兩種方式。一種是網路上你可以查到的轉換為表格，另一種是老司機才知道的定義名稱。

第一種方法，將其轉換為表格，欄標題我命名為“Path”，表格名稱命名為“DynamicPath”。

第二種方法，定義名稱，為單一儲存格或某一範圍命名，方便引用。我這裡是把 B5 儲存格命名為“DynamicPath2”。

第 3 步–

接著不論是轉換為表格或使用定義名稱，接下來的步驟都一樣。 

從 資料→從表格/範圍→右鍵單擊第一行並選擇 向下切入 (Drill Down)，這樣做會將路徑轉換為文本（變量）

第 4 步–

打開進階編輯器，添加黃色部份的 M code

如果是使用定義名稱的話，只有在紅字的地方不一樣。

let 

 Source = Excel.CurrentWorkbook(){[Name="DynamicPath"]}[Content], 

 Column1 = Source{0}[Column1], 

 GetFolderFiles = Folder.Files(Column1) 

in 

 GetFolderFiles

2.在 Power BI 中使用動態路徑

很不幸的是，在 Power BI 中沒有像 Excel 那樣一打開就可以去更新當前檔案所在目錄的函數功能，以致於動態檔案路徑是不可能的。

就算我可以在 BI 裡去導入一個更新當前目錄的 Excel 檔，但是...在 BI 同樣是要用絕對路徑的方式來引入......有沒有一種陷入無解循環的感覺

 💡 此外，實際嘗試使用的結果，那個寫著動態路徑的 excel 檔在引入 BI 時，根本不會被開啟，路徑不會被更新

但是我們仍然可以設定「參數」的方式來設定根目錄路徑，這樣往後換電腦、根目錄換地方，只要修改參數內容，就能夠修改參照的資料來源，勉強算是半自動化吧!

設定流程

1.管理參數─新增參數

2.設定參數「名稱」及「目前的值」

3**.進階編輯器→修改查詢資料表的資料來源**

這樣之後整個資料夾換地方，就只要更改參數 sPath 目前的值即可

在 BI 裡的 M code 就相對單純很多，少了需要向 Excel 載入表格或名稱的步驟

let
  Source = Folder.Files(sPath)
in
  Source