Tổng hợp (gộp) nhiều sheet của 1 file Excel, nhiều file trong folder bằng Power Query nâng cao

Blue Softs epl Liên hệ QC

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,685
Được thích
32,762
Donate (Momo)
Donate
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
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 Source
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
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
    Ketqua
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.

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
    Ketqua

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.
 

File đính kèm

  • MultiSheet.xlsx
    65.3 KB · Đọc: 80
  • Data4Sheet.xlsx
    42.7 KB · Đọc: 68
Lần chỉnh sửa cuối:

thinhnx22

Thành viên hoạt động
Tham gia
22/12/15
Bài viết
165
Được thích
35
Làm theo gợi ý của @excel_lv1.5
Cách 2
PHP:
let
    FName="D:\MyPham\MY BOOK\MCode-PowerQuery\Data4Sheet.xlsx",
    Source = Table.SelectRows(Excel.Workbook(File.Contents(FName), null, true),each ([Kind] = "Table")),
    ListColumns = Table.ColumnNames(Source{2}[Data]),
    Table.ExpandTableColumn(Table.SelectColumns(Source,"Data"),"Data",ListColumns)
 
in
    Ketqua
Cách 1 có hạn chế nên không sửa
Chào chú Mỹ và các anh chị thành viên, cháu mới tìm hiểu và không biết nhiều về power query. Nhờ chú và các thành viên chỉ rõ hơn các bước để ra được kết quả. Cụ thể như sau:
- Sau khi đặt các table range, name tương ứng với các sheet trong Data sheet4, bước tiếp theo append và advance editor như thế nào để đưa được câu lệnh hàm M query để ra được kết quả như bài của chú đưa ra. Nhờ chú và các thành viên hướng dẫn ạ. Cháu cảm ơn.
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,685
Được thích
32,762
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Bạn hãy đọc phần căn bản trong tài liệu Power Pivot- Power query là biết cách làm từng bước. Code trong các bài trong chủ đề này thì copy paste thẳng vào query editor.
 
Web KT
Top Bottom