- Tham gia
- 4/11/07
- Bài viết
- 13,778
- Được thích
- 36,278
- Donate (Momo)
- Giới tính
- Nam
- Nghề nghiệp
- Consultant
I. Tổng hợp (gộp) nhiều sheet trong 1 file
Thông thường và với trình độ căn bản, khi muốn gộp (tổng hợp) nhiều sheet trên cùng 1 file Excel bằng Power query, các bạn tạo mỗi sheet 1 query con, sau đó Append chúng lại với nhau.
Như vậy sẽ có nhiều query con mất công quản lý chúng.
Các cách làm sau đây là chỉ tạo 1 query duy nhất.
Giả sử ta có file Data4Sheet.xlsx nằm ở thư mục D:\MyPham\MY BOOK\MCode-PowerQuery, trong đó có 4 sheet HCM, HN, DN, AG có cùng cấu trúc dữ liệu chuẩn, cùng số cột, thứ tự cột, tên cột, và bắt đầu từ dòng 1, không hề có merge cell, không có dữ liệu bên ngoài cột thừa, dòng thừa. Dữ liệu được định dạng Table sẵn. File đính kèm bên dưới
Cách 1: Dùng câu lệnh hàm M Table.Combine
Câu lệnh combine nối 4 query con nhưng mỗi query con được tạo ra ngay trong query tổng này, Mỗi sheet được lấy ra và gán dòng đầu làm tiêu đề trong 1 câu lệnh. Trong file MultiSheet đính kèm là Sheet Combine.
Cách này phải biết được trong file có bao nhiêu sheet, và tên mỗi sheet mới tạo được. Khi đổi tên sheet sẽ bị lỗi, khi thêm sheet phải sửa code của query thêm 1 dòng tạo query con.
Cách 2: Dùng câu lệnh Table.ExpandColumn từ 1 bảng các Table con
Từ kết quả đầu tiên lấy từ workbook, lọc lấy list (Source) các Dữ liệu dạng Table, chỉ lấy cột có tên [Data], chuyển thành table (List1)
Liệt kê tên cột dữ liệu thành 1 list (ListColumns) từ 1 record của List1 bằng hàm Table.ColumnNames.
Không cần biết List1 có bao nhiêu tên cột, và tên gì, dùng nó trong hàm able.ExpandTableColumn để ra kết quả cuối.
Cách này có ưu điểm là không cần biết file dữ liệu có bao nhiêu sheet, không cần biết tên sheet, cũng không cần biết tên các cột. MultiSheet đính kèm là Sheet MultiSheet. Ngoài ra, khi file dữ liệu gốc có thêm sheet, thì file chứa query tổng hợp chỉ cần refresh, sheet mới tự động thêm vào cuối bảng kết quả. SỬa tên sheet gốc thì query tổng hợp tự cập nhật mà không cần sửa code M. Cách 1 không làm được điều này
Cách 2 có thêm cột: Dùng cấu trúc vòng lặp của M-Code để thêm 1 cột chứa tên sheet.
Dùng hàm List.Count để đếm số dòng của list SheetName, cho biết số sheet lấy được.
Dùng số sheet để tạo vòng lặp n lần tương ứng n sheet. Mỗi vòng lặp lấy 1 table con trong SourceData làm 2 việc:
- Do giả định sheet dữ liệu không định dạng table nên phải lấy Data dạng sheet, và phải dùng hàm PromoteHeaders để lấy dòng 1 làm tiêu đề,
- thêm 1 cột có tên "Tỉnh thành". nội dung là tên sheet tương ứng với table trong vòng lặp, tên sheet lấy từ list SheetName
Ghi chú
Vòng lặp tạo ra bởi hàm List.Transform, bắt đầu từ dòng DataN, chứa 1 cấu trúc let .. in và kết thúc sau in. Trong file đính kèm ở sheet MultiSheetWIthShName
Xem 2 file đính kèm, tải về ở thư mục nào thì sửa đường dẫn trong dòng đầu FName bằng đường dẫn thư mục tải về. Chỉ sửa duy nhất 1 chỗ và duy nhất 1 lần.
Thông thường và với trình độ căn bản, khi muốn gộp (tổng hợp) nhiều sheet trên cùng 1 file Excel bằng Power query, các bạn tạo mỗi sheet 1 query con, sau đó Append chúng lại với nhau.
Như vậy sẽ có nhiều query con mất công quản lý chúng.
Các cách làm sau đây là chỉ tạo 1 query duy nhất.
Giả sử ta có file Data4Sheet.xlsx nằm ở thư mục D:\MyPham\MY BOOK\MCode-PowerQuery, trong đó có 4 sheet HCM, HN, DN, AG có cùng cấu trúc dữ liệu chuẩn, cùng số cột, thứ tự cột, tên cột, và bắt đầu từ dòng 1, không hề có merge cell, không có dữ liệu bên ngoài cột thừa, dòng thừa. Dữ liệu được định dạng Table sẵn. File đính kèm bên dưới
Cách 1: Dùng câu lệnh hàm M Table.Combine
PHP:
let
FName= "D:\MyPham\MY BOOK\MCode-PowerQuery\Data4Sheet.xlsx",
Source = Table.Combine({Table.PromoteHeaders(Excel.Workbook(File.Contents(FName), null, true){[Item="HCM",Kind="Sheet"]}[Data]),
Table.PromoteHeaders(Excel.Workbook(File.Contents(FName), null, true) {[Item="HN",Kind="Sheet"]}[Data]),
Table.PromoteHeaders(Excel.Workbook(File.Contents(FName), null, true){[Item="AG",Kind="Sheet"]}[Data]),
Table.PromoteHeaders(Excel.Workbook(File.Contents(FName), null, true){[Item="DN",Kind="Sheet"]}[Data])})
in SourceCách này phải biết được trong file có bao nhiêu sheet, và tên mỗi sheet mới tạo được. Khi đổi tên sheet sẽ bị lỗi, khi thêm sheet phải sửa code của query thêm 1 dòng tạo query con.
Cách 2: Dùng câu lệnh Table.ExpandColumn từ 1 bảng các Table con
PHP:
let
FName="D:\MyPham\MY BOOK\MCode-PowerQuery\Data4Sheet.xlsx",
Source = Table.SelectRows(Excel.Workbook(File.Contents(FName), null, true),each ([Kind] = "Table"))[Data],
List1 = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ListColumns = Table.ColumnNames(List1{2}[Column1]),
Ketqua = Table.ExpandTableColumn(List1,"Column1",ListColumns)
in
KetquaLiệt kê tên cột dữ liệu thành 1 list (ListColumns) từ 1 record của List1 bằng hàm Table.ColumnNames.
Không cần biết List1 có bao nhiêu tên cột, và tên gì, dùng nó trong hàm able.ExpandTableColumn để ra kết quả cuối.
Cách này có ưu điểm là không cần biết file dữ liệu có bao nhiêu sheet, không cần biết tên sheet, cũng không cần biết tên các cột. MultiSheet đính kèm là Sheet MultiSheet. Ngoài ra, khi file dữ liệu gốc có thêm sheet, thì file chứa query tổng hợp chỉ cần refresh, sheet mới tự động thêm vào cuối bảng kết quả. SỬa tên sheet gốc thì query tổng hợp tự cập nhật mà không cần sửa code M. Cách 1 không làm được điều này
Cách 2 có thêm cột: Dùng cấu trúc vòng lặp của M-Code để thêm 1 cột chứa tên sheet.
PHP:
let
FName="D:\MyPham\MY BOOK\MCode-PowerQuery\Data4Sheet.xlsx",
Source = Table.SelectRows(Excel.Workbook(File.Contents(FName), null, true),each ([Kind] = "Sheet")),
SourceData=Source[Data],
SheetName=Source[Item],
SheetNum={0..List.Count(SheetName)-1},
DataN= List.Transform(SheetNum, (i) =>
let
Data0 = Source[Data]{i},
Datai = Table.AddColumn(Table.PromoteHeaders(Data0), "Tỉnh thành", each SheetName{i})
in Datai),
List1 = Table.FromList(DataN, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ListColumns = Table.ColumnNames(List1{0}[Column1]),
Ketqua = Table.ExpandTableColumn(List1,"Column1",ListColumns)
in
KetquaDùng hàm List.Count để đếm số dòng của list SheetName, cho biết số sheet lấy được.
Dùng số sheet để tạo vòng lặp n lần tương ứng n sheet. Mỗi vòng lặp lấy 1 table con trong SourceData làm 2 việc:
- Do giả định sheet dữ liệu không định dạng table nên phải lấy Data dạng sheet, và phải dùng hàm PromoteHeaders để lấy dòng 1 làm tiêu đề,
- thêm 1 cột có tên "Tỉnh thành". nội dung là tên sheet tương ứng với table trong vòng lặp, tên sheet lấy từ list SheetName
Ghi chú
Vòng lặp tạo ra bởi hàm List.Transform, bắt đầu từ dòng DataN, chứa 1 cấu trúc let .. in và kết thúc sau in. Trong file đính kèm ở sheet MultiSheetWIthShName
Xem 2 file đính kèm, tải về ở thư mục nào thì sửa đường dẫn trong dòng đầu FName bằng đường dẫn thư mục tải về. Chỉ sửa duy nhất 1 chỗ và duy nhất 1 lần.
Lần chỉnh sửa cuối:
