在 Power Query 中參數化數據路徑

如果您已經開始使用 Microsoft Excel 中的免費 Power Query 加載項工具，那麼很快您就會遇到一個高度專業化但非常頻繁且煩人的問題，該問題與源數據的鏈接不斷斷開有關。 問題的本質是，如果在查詢中引用外部文件或文件夾，則 Power Query 會在查詢文本中硬編碼它們的絕對路徑。 在您的計算機上一切正常，但如果您決定將請求的文件發送給您的同事，那麼他們會感到失望，因為。 他們的計算機上的源數據有不同的路徑，我們的查詢將不起作用。

在這種情況下該怎麼辦？ 讓我們通過以下示例更詳細地了解此案例。

問題的形成

假設我們在文件夾中 E:銷售報告 在於文件 前 100 名產品.xls，這是從我們的企業數據庫或 ERP 系統（1C、SAP 等）上傳的。此文件包含有關最受歡迎的商品項目的信息，內部如下所示：

可能一開始就很清楚，在 Excel 中以這種形式使用它幾乎是不可能的：空行到帶有數據的行、合併的單元格、額外的列、多級標題等會干擾。

因此，在同一文件夾中的該文件旁邊，我們創建另一個新文件 處理程序.xlsx，我們將在其中創建一個 Power Query 查詢，該查詢將從源上傳文件中加載醜陋的數據 前 100 名產品.xls, 並將它們按順序排列：

向外部文件發出請求

打開文件 處理程序.xlsx, 在選項卡上選擇 數據 命令 獲取數據 - 從文件 - 從 Excel 工作簿 （數據 - 獲取數據 - 從文件 - 從 Excel），然後指定源文件的位置和我們需要的工作表。 所選數據將加載到 Power Query 編輯器中：

讓我們讓它們恢復正常：

1. 刪除空行 Home——刪除行——刪除空行 （首頁 - 刪除行 - 刪除空行）.
2. 通過刪除不必要的前 4 行 主頁 — 刪除行 — 刪除頂部行 （首頁 - 刪除行 - 刪除頂部行）.
3. 使用按鈕將第一行提升到表頭 使用第一行作為標題 選項卡 首頁 （首頁 - 使用第一行作為標題）.
4. 使用命令將五位數的文章與第二列中的產品名稱分開 拆分列 選項卡 轉型 （變換——拆分列）.
5. 刪除不必要的列並重命名剩餘列的標題以獲得更好的可見性。

結果，我們應該得到以下更令人愉快的畫面：

仍然需要將這個高貴的表上傳回我們文件中的工作表 處理程序.xlsx 團隊 關閉並下載 （首頁 - 關閉並加載） 選項卡 首頁 :

在請求中查找文件的路徑

現在讓我們看看我們的查詢在“幕後”的樣子，使用 Power Query 內置的內部語言，簡潔名稱為“M”。 為此，請在右側窗格中雙擊它返回到我們的查詢 請求和連接 並在選項卡上 評論 選擇 高級編輯器 （查看 - 高級編輯器）:

在打開的窗口中，第二行立即顯示了我們原始上傳文件的硬編碼路徑。 如果我們可以將此文本字符串替換為參數、變量或指向預先寫入此路徑的 Excel 工作表單元格的鏈接，那麼我們以後可以輕鬆更改它。

添加帶有文件路徑的智能表

讓我們暫時關閉 Power Query 並返回到我們的文件 處理程序.xlsx. 讓我們添加一個新的空工作表並在其上製作一個小的“智能”表格，其中唯一的單元格將寫入我們的源數據文件的完整路徑：

要從常規範圍創建智能表，您可以使用鍵盤快捷鍵 按Ctrl+T 或按鈕 格式化為表格 選項卡 首頁 （首頁——表格格式）. 列標題（單元格 A1）絕對可以是任何東西。 另請注意，為清楚起見，我為表格命名 參數 選項卡 構造函數 （設計）.

從資源管理器複製路徑甚至手動輸入當然不是特別困難，但最好盡量減少人為因素並儘可能自動確定路徑。 這可以使用標準的 Excel 工作表函數來實現 CELL （細胞），它可以給出一堆關於作為參數指定的單元格的有用信息——包括當前文件的路徑：

如果我們假設源數據文件總是和我們的處理器在同一個文件夾，那麼我們需要的路徑可以由以下公式形成：

=LEFT(CELL(“filename”);FIND(“[“;CELL(“filename”))-1)&”Top 100 products.xls”

或英文版：

=LEFT(CELL(«filename»);FIND(«[«;CELL(«filename»))-1)&»Топ-100 товаров.xls»

…函數在哪裡 左室SIMV （剩下） 從完整鏈接到左方括號（即當前文件夾的路徑）獲取一段文本，然後將我們的源數據文件的名稱和擴展名粘貼到它上面。

參數化查詢中的路徑

最後也是最重要的一點 - 在請求中寫入源文件的路徑 前 100 名產品.xls，指的是我們創建的“智能”表的單元格 A2 參數.

為此，讓我們返回 Power Query 查詢並再次打開它 高級編輯器 選項卡 評論 （查看 - 高級編輯器）. 而不是引號中的文本字符串路徑 “E：銷售報告前100名產品.xlsx” 讓我們介紹以下結構：

Excel.當前工作簿(){[名稱=“設置”]}[內容]0{}[源數據的路徑]

讓我們看看它是由什麼組成的：

• Excel.當前工作簿() 是M語言的一個函數，用於訪問當前文件的內容
• {[名稱=“設置”]}[內容] – 這是對上一個函數的細化參數，表示我們要獲取“智能”表的內容 參數
• [源數據的路徑] 是表中列的名稱 參數我們提到的
  
• 0{} 是表中的行號 參數我們要從中獲取數據。 上限不計算在內，編號從零開始，而不是從一開始。

事實上，僅此而已。

它仍然可以點擊 完 並檢查我們的請求是如何工作的。 現在，當將包含兩個文件的整個文件夾發送到另一台 PC 時，請求將保持正常運行並自動確定數據的路徑。

• 什麼是 Power Query 以及在 Microsoft Excel 中工作時為什麼需要它
• 如何將浮動文本片段導入 Power Query
• 使用 Power Query 將 XNUMXD 交叉表重新設計為平面表