使用 Power Query 將多個檔案與儲存在單一資料夾中的相同架構合併成一個數據表。 例如,您每個月要合併多個部門的預算活頁簿,其中的欄相同,但每個活頁簿中的列數和值數目不同。 設定完成後,您就可以套用其他轉換,就像套用任何單一匯入的數據源一樣,然後 重新整理數據 以查看每個月的結果。
附註 本主題說明如何合併資料夾中的檔案。 您也可以合併儲存在 SharePoint、Azure Blob 儲存體 和 Azure Data Lake Storage 中的檔案。 這個程式也很類似。
保持簡單:
-
確定您要合併的所有檔案都包含在專用資料夾中,而不會包含其他檔案。 否則,資料夾中的所有檔案以及您選取的任何子資料夾都會包含在要合併的數據中。
-
每個檔案的架構應有一致的欄標題、數據類型和欄數。 欄的順序不必與欄名的比對順序相同。
-
如果可能,請避免數據源中可能有多個數據物件的不相關數據物件,例如 JSON 檔案、Excel 活頁簿或 Access 資料庫。
每一個檔案都遵循簡單的模式,每個檔案中只有一個數據表。
-
選取 [資料 > 從 [檔案夾] 取得數據 > > 取 [從資料夾]。 [ 瀏覽] 對話框隨即出現。
-
找出包含您要合併之檔案的資料夾。
-
資料夾中的檔案清單會出現在 [資料夾路徑 <>] 對話框中。 確認已列出您要的所有檔案。
-
選取對話框底部的其中一個命令,例如 [合併 > 合併 & 載入]。 在 [關於所有這些命令] 一節中討論其他命令。
-
如果您選取任何 [合併] 命令,[ 合併檔案] 對話框就會出現。 若要變更檔案設定,請從 [ 範例檔案 ] 方塊中選取每個檔案,並視需要設定 [檔案來源]、[ 分隔符] 和 [ 數據類型偵測 ]。 您也可以選取或清除對話框底部的 [ 略過有錯誤的檔案 ] 複選框。
-
選取 [確定]。
結果
Power Query 自動建立查詢,將每個檔案的數據合併匯算到工作表中。 建立的查詢步驟和數據行取決於您選擇的命令。 如需詳細資訊,請參閱 關於所有這些查詢一節。
-
選取 [資料 > 從 [檔案夾] 取得數據 > > 取 [從資料夾]。 [ 瀏覽] 對話框隨即出現。
-
找出包含您要合併之檔案的資料夾。
-
資料夾中的檔案清單會出現在 [資料夾路徑 <>] 對話框中。 確認已列出您要的所有檔案。
-
選取對話框底部的其中一個命令,例如 [合併 > 合併 & 轉換]。 在 [關於所有這些命令] 一節中討論其他命令。 隨即出現 Power Query 編輯器。
-
[值] 欄是結構化 的 [列表] 欄。 選取 [ 展開 ] 圖示,然後選取 [ 展開至新列]。
-
[值] 欄現在是結構化的 [記錄] 欄。 選取 [ 展開 ] 圖示。 下拉對話框隨即出現。
-
保留選取的所有欄。 您可以清除 [ 使用原始欄名做為前綴] 複 選框。 選取 [確定]。
-
選取包含數據值的所有欄。 選 取 [常用]、 [移除欄] 旁的箭號,然後選 取 [移除其他欄]。
-
選 取 [首頁 > 關閉 & 載入]。
結果
Power Query 自動建立查詢,將每個檔案的數據合併匯算到工作表中。 建立的查詢步驟和數據行取決於您選擇的命令。 如需詳細資訊,請參閱 關於所有這些查詢一節。
每一個數據源都可以有多個要匯入的物件。 Excel 活頁簿可以有多個工作表、Excel 表格或具名範圍。 Access 資料庫可以有多個數據表和查詢。
-
選取 [資料 > 從 [檔案夾] 取得數據 > > 取 [從資料夾]。 [ 瀏覽] 對話框隨即出現。
-
找出包含您要合併之檔案的資料夾。
-
資料夾中的檔案清單會出現在 [資料夾路徑 <>] 對話框中。 確認已列出您要的所有檔案。
-
選取對話框底部的其中一個命令,例如 [合併 > 合併 & 載入]。 在 [關於所有這些命令] 一節中討論其他命令。
-
在 [ 合併檔案] 對話框中:
-
在 [ 範例檔案] 方塊中,選取要用來建立查詢的範例數據的檔案。 您無法選取物件或只選取物件。 但是,您無法選取多個選項。
-
如果您有許多物件,請使用 [搜尋] 方塊來尋找物件或 顯示選項 ,以及 [ 重新整理 ] 按鈕來篩選清單。
-
選取或清除對話框底部的 [ 略過有錯誤的檔案 ] 複選框。
-
-
選取 [確定]。
結果
Power Query 會自動建立查詢,將每個檔案的數據合併匯算到工作表中。 建立的查詢步驟和數據行取決於您選擇的命令。 如需詳細資訊,請參閱 關於所有這些查詢一節。
如需更多彈性,您可以使用 [合併檔案] 命令,明確地合併 Power Query 編輯器 中的檔案。 假設來源資料夾混合使用檔類型和子資料夾,而您想要以相同檔類型和架構來鎖定特定檔案,但不要針對其他檔案。 這可以改善效能並協助簡化轉換。
-
選取 [數據 > 從 [文件夾] 取得數據 > >。 [ 瀏覽] 對話框隨即出現。
-
找出包含您要合併之檔案的資料夾,然後選取 [ 開啟]。
-
資料夾和子資料夾中所有檔案的清單會出現在 [ 資料夾路徑<> ] 對話框中。 確認已列出您要的所有檔案。
-
選取底部的 [轉換數據 ]。 Power Query 編輯器 隨即開啟並顯示資料夾和任何子資料夾中的所有檔案。
-
若要選取您要的檔案,請篩選欄,例如 [擴充功能] 或 [資料夾路徑]。
-
若要將檔案合併成單一數據表,請選取包含每個二進位 (通常是第一欄) 的內容欄,然後選取 [常用] > [合併檔案]。 [ 合併檔案] 對話框隨即出現。
-
Power Query 根據預設,分析清單中的第一個檔案範例檔案,以使用正確的連接器並識別相符的欄。
若要針對範例檔案使用不同的檔案,請從 [ 範例檔案 ] 下拉式清單中選取該檔案。 -
您也可以選擇在底部選取 [ 略過有錯誤的檔案 ],將這些檔案從結果中排除。
-
選取 [確定]。
結果
Power Query 會自動建立查詢,將每個檔案的數據合併匯算到工作表中。 建立的查詢步驟和數據行取決於您選擇的命令。 如需詳細資訊,請參閱 關於所有這些查詢一節。
您可以選取數個命令,每個命令都有不同的用途。
-
合併及轉換數據 若要將所有檔案與查詢合併,然後啟動 Power Query 編輯器,請選取 [合併 > 合併及轉換數據]。
-
合併和載入 若要顯示 [ 範例 檔案] 對話框,請建立查詢,然後載入至工作表,選取 [合併 > 合併及載入]。
-
合併並載入至 若要顯示 [ 範例 檔案] 對話框,請建立查詢,然後顯示 [ 匯 入] 對話框,選取 [ 合併 > 合併及載入至]。
-
負荷 若要使用一個步驟建立查詢,然後載入至工作表,請選取 [ 載入 > 載入]。
-
載入至 若要使用單步驟建立查詢,然後顯示 [匯 入 ] 對話框,請選取 [ 載入 > 載入至]。
-
轉換數據若要使用單步驟建立查詢,然後啟動 Power Query 編輯器,請選取 [轉換數據]。
不過,您會在合併檔案時,會在 [查詢 ] 窗格 的 [說明查詢] 群組下建立數個支持查詢。
-
Power Query 會根據範例查詢建立「範例檔案」查詢。
-
「轉換檔案」函數查詢使用「參數1」查詢來指定每個檔案 (或二進位) 做為「範例檔案」查詢的輸入。 此查詢也會建立包含檔案內容 的內容 列,並自動展開結構化的 [記錄] 欄,將欄數據新增至結果。 「轉換檔案」和「範例檔案」查詢是連結的,因此「轉換檔案」查詢會反映對「範例檔案」查詢的變更。
-
包含最終結果的查詢位於 [其他查詢] 群組中。 根據預設,它會以您從中匯入檔案的資料夾命名。
如需進一步調查,請以滑鼠右鍵按兩下每個查詢,然後選取 [編輯 ] 以檢查每個查詢步驟,並查看查詢如何協同運作。