Nhập dữ liệu từ một thư mục có nhiều tệp (Power Query)

Sử Power Query để kết hợp nhiều tệp với cùng một sơ đồ được lưu trữ trong một thư mục vào một bảng. Ví dụ: mỗi tháng bạn muốn kết hợp sổ làm việc ngân sách từ nhiều phòng ban, trong đó các cột giống nhau nhưng số hàng và giá trị khác nhau ở mỗi sổ làm việc. Sau khi thiết lập, bạn có thể áp dụng các phương pháp chuyển đổi bổ sung như bạn làm với bất kỳ nguồn dữ liệu được nhập duy nhất nào, rồi làm mới dữ liệu để xem kết quả cho mỗi tháng.

Tổng quan khái niệm về Kết hợp tệp thư mục

Ghi chú Chủ đề này cho biết cách kết hợp các tệp từ một thư mục. Bạn cũng có thể kết hợp các tệp được lưu trữ trong SharePoint, Azure Blob Storage và Azure Data Lake Storage. Quy trình này tương tự như vậy.

Phiên bản mới hơn Office 2013

Đơn giản:

Đảm bảo rằng tất cả các tệp bạn muốn kết hợp đều nằm trong một thư mục chuyên dụng mà không cần tệp không liên quan. Nếu không, tất cả các tệp trong thư mục và bất kỳ thư mục con nào bạn chọn sẽ được bao gồm trong dữ liệu được kết hợp.
Mỗi tệp phải có cùng một sơ đồ với tiêu đề cột, kiểu dữ liệu và số cột nhất quán. Các cột không nhất thiết phải theo cùng thứ tự với tên cột thực hiện khớp.
Nếu có thể, hãy tránh các đối tượng dữ liệu không liên quan đối với các nguồn dữ liệu có thể có nhiều đối tượng dữ liệu, chẳng hạn như tệp JSON, sổ làm việc Excel hoặc cơ sở dữ liệu Access.

Mỗi tệp này đều theo một mẫu hình đơn giản, chỉ có một bảng dữ liệu trong mỗi tệp.

Chọn Dữ > Lấy Dữ liệu > từ tệp > từ thư mục. Hộp thoại Duyệt sẽ xuất hiện.
Định vị thư mục chứa các tệp bạn muốn kết hợp.
Danh sách các tệp trong thư mục sẽ xuất hiện trong hộp thoại <Dẫn> thư mục. Xác minh rằng tất cả các tệp bạn muốn đều được liệt kê.
Chọn một trong các lệnh ở dưới cùng của hộp thoại, ví dụ: Kết hợp lệnh> và & tải. Có các lệnh bổ sung được thảo luận trong mục Giới thiệu về tất cả các lệnh đó.
Nếu bạn chọn lệnh Kết hợp bất kỳ, hộp thoại Kết hợp Tệp sẽ xuất hiện. Để thay đổi cài đặt tệp, hãy chọn từng tệp từ hộp Tệp Mẫu, đặt Nguồn gốc Tệp,Dấu tách và Phát hiện Loại Dữ liệu như mong muốn. Bạn cũng có thể chọn hoặc bỏ chọn hộp kiểm Bỏ qua các tệp có lỗi ở cuối hộp thoại.
Chọn OK.

Kết quả

Power Query tự động tạo truy vấn để hợp nhất dữ liệu từ từng tệp vào một trang tính. Các bước truy vấn và cột được tạo tùy thuộc vào lệnh bạn chọn. Để biết thêm thông tin, hãy xem phần Giới thiệu về tất cả các truy vấn đó.

Chọn Dữ > Lấy Dữ liệu > từ tệp > từ thư mục. Hộp thoại Duyệt sẽ xuất hiện.
Định vị thư mục chứa các tệp bạn muốn kết hợp.
Danh sách các tệp trong thư mục sẽ xuất hiện trong hộp thoại <Dẫn> thư mục. Xác minh rằng tất cả các tệp bạn muốn đều được liệt kê.
Chọn một trong các lệnh ở dưới cùng của hộp thoại, ví dụ: Kết hợp> đổi & đổi. Có các lệnh bổ sung được thảo luận trong mục Giới thiệu về tất cả các lệnh đó.

Biểu Trình soạn thảo Power Query xuất hiện.
Cột Giá trị là cột Danh sách có cấu trúc. Chọn biểu tượng mở rộng, rồi chọn Bung rộng sang Hàng mới.
Cột Giá trị giờ đây là cột Bản ghi có cấu trúc. Chọn biểu tượng Bung trang. Hộp thoại thả xuống sẽ xuất hiện.
Duy trì chọn tất cả các cột. Bạn có thể muốn bỏ chọn hộp kiểm Sử dụng tên cột gốc làm tiền tố. Chọn OK.
Chọn tất cả các cột có chứa giá trị dữ liệu. Chọn Trang đầu, mũi tên bên cạnh Loại bỏ Cột, rồi chọn Loại bỏ Cột Khác.
Chọn Trang > Đóng & Tải.

Kết quả

Mỗi nguồn dữ liệu này có thể có nhiều đối tượng để nhập. Sổ làm việc Excel có thể có nhiều trang tính, bảng Excel hoặc phạm vi đã đặt tên. Cơ sở dữ liệu Access có thể có nhiều bảng và truy vấn.

Chọn Dữ > Lấy Dữ liệu > từ tệp > từ thư mục. Hộp thoại Duyệt sẽ xuất hiện.
Định vị thư mục chứa các tệp bạn muốn kết hợp.
Danh sách các tệp trong thư mục sẽ xuất hiện trong hộp thoại <Dẫn> thư mục. Xác minh rằng tất cả các tệp bạn muốn đều được liệt kê.
Chọn một trong các lệnh ở dưới cùng của hộp thoại, ví dụ: Kết hợp lệnh> và & tải. Có các lệnh bổ sung được thảo luận trong mục Giới thiệu về tất cả các lệnh đó.
Trong hộp thoại Kết hợp Tệp:
- Trong hộp Tệp Mẫu , chọn một tệp để sử dụng làm dữ liệu mẫu được sử dụng để tạo truy vấn. Bạn không thể chọn một đối tượng hoặc chỉ chọn một đối tượng. Tuy nhiên, bạn không thể chọn nhiều hơn một mục.
- Nếu bạn có nhiều đối tượng, hãy sử dụng hộp Tìm kiếm để định vị một đối tượng hoặc Tùy chọn Hiển thị cùng với nút Làm mới để lọc danh sách.
- Chọn hoặc bỏ chọn hộp kiểm Bỏ qua các tệp có lỗi ở cuối hộp thoại.
Chọn OK.

Kết quả

Power Query tự động tạo một truy vấn để hợp nhất dữ liệu từ từng tệp vào một trang tính. Các bước truy vấn và cột được tạo tùy thuộc vào lệnh bạn chọn. Để biết thêm thông tin, hãy xem phần Giới thiệu về tất cả các truy vấn đó.

Để linh hoạt hơn, bạn có thể kết hợp tệp trong hộp thoại một Trình soạn thảo Power Query cách rõ ràng bằng cách sử dụng lệnh Kết hợp Tệp. Giả sử thư mục nguồn có một hỗn hợp các loại tệp và thư mục con và bạn muốn nhắm mục tiêu các tệp cụ thể có cùng loại tệp và sơ đồ nhưng không phải các tệp khác. Điều này có thể cải thiện hiệu suất và giúp đơn giản hóa các chuyển đổi của bạn.

Chọn Dữ > Lấy Dữ liệu > từ tệp >từ thư mục. Hộp thoại Duyệt sẽ xuất hiện.
Định vị thư mục chứa các tệp bạn muốn kết hợp, rồi chọn Mở.
Danh sách tất cả các tệp trong thư mục và thư mục con sẽ xuất hiện trong hộp thoại<Dẫn> mục. Xác minh rằng tất cả các tệp bạn muốn đều được liệt kê.
Chọn Chuyển đổi Dữ liệu ở dưới cùng. Tệp Trình soạn thảo Power Query mở ra và hiển thị tất cả các tệp trong thư mục và mọi thư mục con.
Để chọn tệp bạn muốn, hãy lọc các cột, chẳng hạn như Phần mở rộng hoặc Đường dẫn Thư mục.
Để kết hợp các tệp vào một bảng duy nhất, hãy chọn cột Nội dung có chứa mỗi Nhị phân (thường là cột đầu tiên), rồi chọn Trang đầu > Hợp Tệp. Hộp thoại Kết hợp Tệp xuất hiện.
Power Query phân tích tệp ví dụ, theo mặc định, tệp đầu tiên trong danh sách sẽ sử dụng đúng bộ nối và xác định các cột khớp.

Để sử dụng một tệp khác cho tệp ví dụ, hãy chọn tệp đó từ danh sách thả xuống Tệp Mẫu.
Hoặc, ở dưới cùng, chọn Bỏ qua các tệp có lỗis để loại trừ các tệp đó khỏi kết quả.
Chọn OK.

Kết quả

Có một số lệnh bạn có thể chọn và mỗi lệnh có một mục đích khác nhau.

Kết hợp và Biến đổi Dữ liệu Để kết hợp tất cả các tệp với một truy vấn, rồi khởi chạy ứng dụng Trình soạn thảo Power Query, hãy chọn Kết hợp các> Kết hợp và Chuyển đổi Dữ liệu.
Kết hợp và Tải Để hiển thị hộp thoại Tệp mẫu, hãy tạo một truy vấn, rồi tải vào trang tính, chọn Kết hợp các> Kết hợp và Tải.
Kết hợp và Tải Đến Để hiển thị hộp thoại Tệp mẫu, hãy tạo một truy vấn, rồi hiển thị hộp thoại Nhập , chọn Kết hợp> Kết hợp và Tải Đến.
Tải Để tạo truy vấn với một bước, rồi tải vào trang tính, hãy chọn Tải> Tải.
Tải Đến Để tạo truy vấn với một bước, rồi hiển thị hộp thoại Nhập, chọn Tải> Tải Đến.
Chuyển đổi Dữ liệuĐể tạo truy vấn với một bước, rồi khởi chạy truy vấn, Trình soạn thảo Power Query Chuyển đổi Dữ liệu.

Tuy nhiên, bạn kết hợp các tệp, một số truy vấn hỗ trợ sẽ được tạo trong ngăn Truy vấn bên dưới nhóm "Truy vấn Trợ giúp".

Danh sách các truy vấn được tạo trong ngăn Truy vấn

Power Query tạo truy vấn "Tệp Mẫu" dựa trên truy vấn ví dụ.
Truy vấn hàm "Chuyển đổi Tệp" sử dụng truy vấn "Tham số 1" để chỉ định từng tệp (hoặc nhị phân) làm dữ liệu đầu vào truy vấn "Tệp Mẫu". Truy vấn này cũng tạo cột Nội dung có chứa nội dung tệp và tự động bung rộng cột Bản ghi có cấu trúc để thêm dữ liệu cột vào kết quả. Các truy vấn "Chuyển đổi Tệp" và "Tệp Mẫu" được liên kết để các thay đổi đối với truy vấn "Tệp Mẫu" được phản ánh trong truy vấn "Tệp Chuyển đổi".
Truy vấn có chứa kết quả cuối cùng nằm trong nhóm "Truy vấn khác". Theo mặc định, thư mục này được đặt tên theo thư mục mà bạn đã nhập các tệp từ đó.

Để điều tra thêm, hãy bấm chuột phải vào từng truy vấn và chọn Chỉnh sửa để kiểm tra từng bước truy vấn và xem cách truy vấn hoạt động trong kết hợp.

Để bắt đầu quy trình kết hợp nhiều tệp, trước tiên hãy đảm bảo rằng tất cả các tệp bạn muốn kết hợp đều nằm trong một thư mục tệp duy nhất.

Lưu ý: Hỗ trợ các tệp Excel, Văn bản, CSV, JSON, XML và Access.

Bấm vào Power Query tab, sau đó bấm Từ Thư > Từ Thư mục.
Bấm vào nút Duyệt để định vị thư mục của bạn.

Danh sách các tệp trong thư mục sẽ xuất hiện. Chọn tùy chọn Chỉnh sửa để mở Trình soạn thảo truy vấn. Trong trường hợp này, chúng tôi đã tải 4 sổ làm việc Excel.
Sau khi bạn đã xác minh rằng tất cả các tệp bạn muốn được liệt kê, hãy bấm nút Kết hợp Nhị phân từ cột Nội dung.
Mỗi tệp được phân tích và định dạng tệp chính xác được xác định, chẳng hạn như văn bản, Excelhoặc JSON. Trong trường hợp này, một danh sách các trang tính từ sổ làm việc Excel đầu tiên sẽ được hiển thị. Chọn trang tính bạn muốn sử dụng, rồi bấm OK.
Sau đó, quy trình chuyển đổi nhị phân kết hợp sẽ tự động tạo một truy vấn để hợp nhất dữ liệu từ từng tệp và hiển thị bản xem trước. Nếu bạn đã hài lòng với kết quả, hãy bấm Đóng & tải.

Sau đó, quy trình kết hợp các nhị phân sẽ hợp nhất các trang tính được liệt kê vào một trang tính duy nhất.
Nếu tệp dữ liệu gốc thay đổi, bạn luôn có thể làm mới dữ liệu đã nhập. Bấm vào bất kỳ đâu trong phạm vi dữ liệu, sau đó đến Công cụ Truy vấn > làm mới. Ngoài ra, bạn có thể dễ dàng áp dụng các bước chuyển đổi hoặc trích xuất bổ sung bằng cách sửa đổi truy vấn gương mẫu được tạo tự động mà không phải lo lắng về việc sửa đổi hoặc tạo các bước truy vấn hàm bổ sung; bất kỳ thay đổi nào đối với truy vấn gương mẫu sẽ được tự động tạo ra trong truy vấn hàm được liên kết.

Sự cố đã biết

Khi tải thủ tục nhị phân kết hợp vào Excel, bạn có thể thấy thông báo lỗi sau:

Kết hợp thông báo lỗi Nhị phân. Đây là một lỗi đã biết đang được giải quyết.

Nếu bạn loại bỏ (các) lỗi bằng cách bấm OK, quy trình kết hợp các nhị phân sẽ hoàn tất và tải dữ liệu của bạn đúng cách bất chấp lỗi. Chúng tôi đang nỗ lực sửa lỗi.