問題的形成
我們在一個文件夾中有多個文件（在我們的示例中 – 4 個，在一般情況下 – 任意數量） 銷售報告:
在內部，這些文件如下所示：
其中：
- 我們需要的數據表總是叫 照片, 但可以在工作簿中的任何位置。
- 超越單子 照片 每本書可能有其他頁。
- 包含數據的表具有不同的行數，並且可能以工作表上的不同行開頭。
- 不同表中相同列的名稱可能不同（例如， 數量 = 數量 = 數量).
- 表格中的列可以按不同的順序排列。
任務：從工作表中的所有文件中收集銷售數據 照片 到一個公用表中，以便隨後在其上構建摘要或任何其他分析。
步驟 1. 準備列名目錄
首先要做的是準備一本參考書，其中包含列名的所有可能選項及其正確解釋：
我們使用選項卡上的“格式為表格”按鈕將此列表轉換為動態“智能”表格 首頁 （首頁——表格格式） 或鍵盤快捷鍵 按Ctrl+T 並使用命令將其加載到 Power Query 數據 – 來自表/範圍 （數據——來自表/範圍）. 在最新版本的 Excel 中，它已重命名為 有葉子 （來自工作表）.
在 Power Query 查詢編輯器窗口中，我們通常刪除步驟 更改類型 並通過單擊按鈕添加一個新步驟而不是它 fx在編輯欄中（如果它不可見，則可以在選項卡上啟用它 評論) 並在內置 Power Query 語言 M 中輸入公式：
=Table.ToRows（來源）
此命令將轉換上一步中加載的 資源 將表引用到由嵌套列表（List）組成的列表中，每個列表又是一對值 它是-成為 從一行：
稍後，當從所有加載的表中批量重命名標題時，我們將需要這種類型的數據。
完成轉換後，選擇命令 主頁 — 關閉並加載 — 關閉並加載... 和進口類型 只需創建一個連接 (Home — Close&Load — Close&Load to... — 僅創建連接) 並返回 Excel。
步驟 2. 我們從所有文件中加載所有內容
現在讓我們從文件夾中加載我們所有文件的內容——現在，照原樣。 選擇團隊 數據 - 獲取數據 - 從文件 - 從文件夾 （數據 - 獲取數據 - 從文件 - 從文件夾） 然後是我們的源書所在的文件夾。
在預覽窗口中，單擊 轉 （轉換） or 更改 （編輯）:
然後展開所有下載文件的內容 （二進制） 列標題中帶有雙箭頭的按鈕 內容:
Power Query 上的第一個文件的示例（東方號.xlsx) 將詢問我們要從每個工作簿中獲取的工作表的名稱 - 選擇 照片 然後按確定：
在那之後（事實上），會發生幾個對用戶來說並不明顯的事件，其後果在左側面板中清晰可見：
- Power Query 將從文件夾中獲取第一個文件（我們將擁有它 東方號.xlsx — 看到 文件示例) 作為示例並通過創建查詢來導入其內容 轉換示例文件. 此查詢將有一些簡單的步驟，例如 資源 （文件訪問） 導航 （紙張選擇）並可能提高標題。 此請求只能從一個特定文件加載數據 東方號.xlsx.
- 基於此請求，將創建與其關聯的函數 轉換文件 （由特徵圖標表示 fx)，其中源文件將不再是一個常量，而是一個變量值——一個參數。 因此，此函數可以從我們作為參數滑入其中的任何書籍中提取數據。
- 該函數將依次應用於列中的每個文件（二進制） 內容 – step 對此負責 調用自定義函數 在我們的查詢中，將一列添加到文件列表中 轉換文件 從每個工作簿導入結果：
- 多餘的列被刪除。
- 嵌套表的內容被展開（步驟 擴展表列)——我們看到了所有書籍數據收集的最終結果：
步驟 3. 打磨
前面的截圖清楚地表明，“按原樣”直接組裝結果質量很差：
- 列顛倒了。
- 許多額外的行（空而不是唯一的）。
- 表格標題不被視為標題，而是與數據混合在一起。
您可以非常輕鬆地解決所有這些問題 - 只需調整轉換示例文件查詢。 我們對其進行的所有調整都將自動落入相關的轉換文件功能中，這意味著它們將在以後從每個文件中導入數據時使用。
通過打開請求 轉換示例文件，添加步驟以過濾不必要的行（例如，按列 Column2) 並使用按鈕提升標題 使用第一行作為標題 （使用第一行作為標題）. 桌子看起來會好很多。
為了使來自不同文件的列在以後自動適合彼此，它們必須命名相同。 您可以使用一行 M 代碼根據先前創建的目錄執行這樣的批量重命名。 讓我們再次按下按鈕 fx 在公式欄中並添加要更改的函數：
= Table.RenameColumns(#”提升的標題”, Headers, MissingField.Ignore)
此函數從上一步中獲取表 高架標頭 並根據嵌套查找列表重命名其中的所有列 標題. 第三個論點 缺失字段.忽略 需要，以便在目錄中但不在表中的那些標題上，不會發生錯誤。
其實，僅此而已。
返回請求 銷售報告 我們將看到完全不同的畫面——比上一張要好得多：
- 什麼是 Power Query、Power Pivot、Power BI 以及 Excel 用戶需要它們的原因
- 從給定文件夾中的所有文件中收集數據
- 將書中所有工作表中的數據收集到一個表中