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

Liên hệ QC

ptm0412

Bad Excel Member
Thành viên BQT
Administrator
Tham gia
4/11/07
Bài viết
14,449
Được thích
37,112
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: 223
  • Data4Sheet.xlsx
    42.7 KB · Đọc: 205
Lần chỉnh sửa cuối:
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.
 
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.
 
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.
Mình thấy có cách này cũng hay nè. Chỉ cần rõ hàm Excel.CurrentWorkbook() và filter cột Content sẽ gộp lại được nhiều sheet.
CODE:
let
Source = Excel.CurrentWorkbook(),
#"Expanded Content" = Table.ExpandTableColumn(Source, "Content", {"Số chứng từ", "Ngày", "Mã KH", "Mã mặt hàng", "Đvt", "Số lượng", "Đơn giá", "Giảm", "Thành tiền", "Tiền giảm", "Còn lại", "Diễn giải"}, {"Content.Số chứng từ", "Content.Ngày", "Content.Mã KH", "Content.Mã mặt hàng", "Content.Đvt", "Content.Số lượng", "Content.Đơn giá", "Content.Giảm", "Content.Thành tiền", "Content.Tiền giảm", "Content.Còn lại", "Content.Diễn giải"})
in
#"Expanded Content"

1659493446670.png1659493469137.png
 
Mình thấy có cách này cũng hay nè.
Cách này:
- Chưa thấy câu lệnh lọc lấy sheet hay lấy table. Nếu đã tạo table thì sẽ tồn tại cả sheet và cả table, nên sẽ lấy 2 lần.
- Khi lấy xong, không biết dữ liệu dòng nào có nguồn từ sheet nào do không có thêm cột tên sheet
- Một code này chỉ lấy từ 1 file ấn định,lấy từ file khác không được
 
Lần chỉnh sửa cuối:
Cách này:
- Chưa thấy câu lệnh lọc lấy sheet hay lấy table. Nếu đã tạo table thì sẽ tồn tại cả sheet và cả table, nên sẽ lấy 2 lần.
- Khi lấy xong, không biết dữ liệu dòng nào có nguồn từ sheet nào do không có thêm cột tên sheet
- Một code này chỉ lấy từ 1 file ấn định,lấy từ file khác không được
Mình chưa rành CODE, mà mình thao tác bằng các chức năng có sẵn (Chỉ rõ hàm Excel.CurrentWorkbook()). Mình thấy nó có cột Name và khi thêm sheet mới nó cũng có luôn
1659603632492.png
 
Mình chưa rành CODE, mà mình thao tác bằng các chức năng có sẵn (Chỉ rõ hàm Excel.CurrentWorkbook()). Mình thấy nó có cột Name và khi thêm sheet mới nó cũng có luôn
Bạn đúng ở gạch đầu dòng thứ 2. Còn 2 cái nữa. Với lại bạn trích dẫn code ở 1 bài lấy dữ liệu từ 1 file bất kỳ đang đóng, chứ không phải lấy từ chính file chứa query.
 
Bạn đúng ở gạch đầu dòng thứ 2. Còn 2 cái nữa. Với lại bạn trích dẫn code ở 1 bài lấy dữ liệu từ 1 file bất kỳ đang đóng, chứ không phải lấy từ chính file chứa query.
File đó mình tự thao tác. Không phải lấy từ nguồn nào đâu. Mình thao tác xong mình vào Advanced Editor copy mã CODE past vào diễn đàn á.
1659606894979.png
 

File đính kèm

  • Data4Sheet.xlsx
    73.1 KB · Đọc: 9
File đó mình tự thao tác. Không phải lấy từ nguồn nào đâu. Mình thao tác xong mình vào Advanced Editor copy mã CODE past vào diễn đàn á.
Bài 24 của bạn, và bạn trích dẫn bài 1 và code bài 1 lấy dữ liệu từ file khác đang đóng. Code của bạn, file của bạn nhìn là biết rồi mà.
 
Bài 24 của bạn, và bạn trích dẫn bài 1 và code bài 1 lấy dữ liệu từ file khác đang đóng. Code của bạn, file của bạn nhìn là biết rồi mà.
À, đúng rồi. Bài 1 của mình làm xong mình không có lưu lại. Xong mình xem bạn comment nên mình tải lại và làm lại.
 
II. TỔNG HỢP TỪ 1 FOLDER NHIỀU FILE, MỖI FILE NHIỀU SHEET
Dùng 2 vòng lặp lồng nhau, lấy hết các file trong folder, mỗi file lấy hết sheet. Điều kiện là tất cả sheet trong tất cả file là dữ liệu đúng chuẩn.
Có thể thêm bớt file, thêm bớt sheet, đổi tên file, đổi tên sheet tuỳ ý
Có thể thư mục bất kỳ với dữ liệu bất kỳ.

JavaScript:
let
  
    FolderName ="D:\MyPham\MY BOOK\MCode-PowerQuery\Data",
    FileNameList= Folder.Files(FolderName)[Name],
    FileNum={0..List.Count(FileNameList)-1},
    DataF= List.Transform(FileNum, (f) =>
    let
        FFullName =FolderName & "\" & FileNameList{f},
        Source1 = Table.SelectRows(Excel.Workbook(File.Contents(FFullName), null, true),each ([Kind] = "Sheet")),
        SourceData=Source1[Data],
        SheetName=Source1[Name],
        SheetNum={0..List.Count(SheetName)-1},
        DataN= List.Transform(SheetNum, (i) =>
        let
            Data0 = (SourceData{i}),
            DataFName = Table.AddColumn(Table.PromoteHeaders(Data0), "File", each FileNameList{f}),
            Datai = Table.AddColumn(DataFName, "Sheet", each SheetName{i})
        in Datai),
        List1 =  Table.FromList(DataN, Splitter.SplitByNothing()),
        ListColumns = Table.ColumnNames(List1{0}[Column1]),
        Ketqua1 = Table.ExpandTableColumn(List1,"Column1",ListColumns)
    in Ketqua1),
    List2 =Table.FromList(DataF,Splitter.SplitByNothing()),
    ListColumns2 = Table.ColumnNames(DataF{0}),
    Ketqua = Table.ExpandTableColumn(List2, "Column1", ListColumns2)
  
in Ketqua
Giải nén thư mục Data, lấy đường dẫn thư mục sửa vào dòng 1 của query.
Em chào thầy Mỹ, nhờ thầy xem giúp em chỗ này với ạ, khi close and load thì báo lỗi như vậy, loay hoay mãi mà em vẫn chưa tìm ra nguyên nhân.

1665982840704.png
 
Bạn lấy nguồn từ đâu? Ghi đầy đủ đường dẫn nguồn và code tôi xem thử
Code của em sử dụng như thầy hướng dẫn, em chỉ thay lại đường dẫn bằng thực mục Data chứa các file con mà em muốn tập hợp, chỉ khi nhấn Close and Load thì em thấy báo lỗi.

let
FolderName ="C:\Users\HT\Desktop\DATA",
FileNameList= Folder.Files(FolderName)[Name],
FileNum={0..List.Count(FileNameList)-1},
DataF= List.Transform(FileNum, (f) =>
let
FFullName =FolderName & "\" & FileNameList{f},
Source1 = Table.SelectRows(Excel.Workbook(File.Contents(FFullName), null, true),each ([Kind] = "Sheet")),
SourceData=Source1[Data],
SheetName=Source1[Name],
SheetNum={0..List.Count(SheetName)-1},
DataN= List.Transform(SheetNum, (i) =>
let
Data0 = (SourceData{i}),
DataFName = Table.AddColumn(Table.PromoteHeaders(Data0), "File", each FileNameList{f}),
Datai = Table.AddColumn(DataFName, "Sheet", each SheetName{i})
in Datai),
List1 = Table.FromList(DataN, Splitter.SplitByNothing()),
ListColumns = Table.ColumnNames(List1{0}[Column1]),
Ketqua1 = Table.ExpandTableColumn(List1,"Column1",ListColumns)
in Ketqua1),
List2 =Table.FromList(DataF,Splitter.SplitByNothing()),
ListColumns2 = Table.ColumnNames(DataF{0}),
Ketqua = Table.ExpandTableColumn(List2, "Column1", ListColumns2)

in Ketqua
 
Xin chào anh, em đang sử dụng bộ Office Standard 2016, bản 64 bit ạ.

View attachment 282241
Thực tế tôi cũng từng gặp lỗi này ở bộ Office 2016 64-bit.
Sau một thời gian tìm nguyên nhân, tải bản vá lỗi này thì ổn, bạn xem thử update theo link này nhé.


Hy vọng sửa được lỗi này
Cảm ơn bạn.
 
Thực tế tôi cũng từng gặp lỗi này ở bộ Office 2016 64-bit.
Sau một thời gian tìm nguyên nhân, tải bản vá lỗi này thì ổn, bạn xem thử update theo link này nhé.


Hy vọng sửa được lỗi này
Cảm ơn bạn.
Em cảm ơn anh rất nhiều, em đã cài bản update và không còn bị lỗi nữa. :clap2:
 
Web KT

Bài viết mới nhất

Back
Top Bottom