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,687
Được thích
32,763
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:

excel_lv1.5

Thành viên tích cực
Tham gia
20/10/17
Bài viết
929
Được thích
1,706
Giới tính
Nam
Tôi góp ý một tý:
Cách 1: nên đưa source excel vào một biến rồi khi combine gọi, như vậy code sẽ ngắn và chỉ connect lần duy nhất, cách của bạn nó đang connect tới 4 lần
Cách 2: Sau bước source là có thể expandcolumn rồi không cần tạo List, có thể thêm bước filter để lấy sheet hay table thôi
1627581077494.png
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,687
Được thích
32,763
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Tôi góp ý một tý:
Cách 1: nên đưa source excel vào một biến rồi khi combine gọi, như vậy code sẽ ngắn và chỉ connect lần duy nhất, cách của bạn nó đang connect tới 4 lần
Cách 2: Sau bước source là có thể expandcolumn rồi không cần tạo List, có thể thêm bước filter để lấy sheet hay table thôi
Cám ơn bạn đã gợi ý. Cách 1 là cách tôi làm kiểu record macro của VBA rồi chỉnh sửa, chưa có đầu tư suy nghĩ nhiều. Sau đó khi rảnh rỗi mới nghĩ ra cách 2 và 2+. Phải nói rằng nhờ hướng dẫn về vòng lặp của bạn và @Hau151978 trong chủ đề giải thích vòng lặp mà tôi làm được việc thêm cột trong 2+. Quá trình làm file trên là cả 1 quá trình mày mò đúng/ sai/ sửa nên chưa tối ưu.
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,687
Được thích
32,763
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
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
 
Lần chỉnh sửa cuối:

kelacloi

Thành viên hoạt động
Tham gia
6/11/14
Bài viết
191
Được thích
51
Tôi góp ý một tý:
Cách 1: nên đưa source excel vào một biến rồi khi combine gọi, như vậy code sẽ ngắn và chỉ connect lần duy nhất, cách của bạn nó đang connect tới 4 lần
Cách 2: Sau bước source là có thể expandcolumn rồi không cần tạo List, có thể thêm bước filter để lấy sheet hay table thôi
View attachment 263296
Bình thường khi em load vào Query thì có thể dùng một Table nhiều lần, thế thì dùng reference nó sẽ connect nhiều lần hay là chỉ connect một lần a?
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,687
Được thích
32,763
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Bình thường khi em load vào Query thì có thể dùng một Table nhiều lần, thế thì dùng reference nó sẽ connect nhiều lần hay là chỉ connect một lần a?
Tôi hiểu gợi ý của @excel_lv1.5 về dùng biến là tạo 1 biến connect toàn bộ file và sửa như sau:
PHP:
let
    FName= "D:\MyPham\MY BOOK\MCode-PowerQuery\Data4Sheet.xlsx",
    Source1 =Excel.Workbook(File.Contents(FName), null, true),
    Source = Table.Combine({Table.PromoteHeaders(Source1{[Item="HCM",Kind="Sheet"]}[Data]),
            Table.PromoteHeaders(Source1{[Item="HN",Kind="Sheet"]}[Data]),
            Table.PromoteHeaders(Source1{[Item="AG",Kind="Sheet"]}[Data]),
            Table.PromoteHeaders(Source1{[Item="DN",Kind="Sheet"]}[Data])})
in Source
Theo tôi nhận xét thì chỉ connect 1 lần. Có lẽ nó khớp được cái mà bạn gọi là reference, hay bạn có ý khác?
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,687
Được thích
32,763
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
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.
 

File đính kèm

  • Data.zip
    70.5 KB · Đọc: 57
  • Folder-MultiSheet.xlsx
    59.2 KB · Đọc: 59
Lần chỉnh sửa cuối:

doanlong49

Thành viên mới
Tham gia
12/7/13
Bài viết
31
Được thích
13
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.

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.
Cho hỏi tại sao không sử dụng function tổng hợp cho đơn giản! Mà phải viết M Code phức tạp vậy?
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,687
Được thích
32,763
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Cho hỏi tại sao không sử dụng function tổng hợp cho đơn giản! Mà phải viết M Code phức tạp vậy?
Thứ nhất: Folder nhiều file nhưng không cần mở file, thứ hai file nhiều sheet không cần tên sheet, thứ ba Sheet bao nhiêu cột, cột tên gì không cần biết.
Quan trọng hơn hết: Nếu tổng số dòng của tất cả file, tất cả sheet mà vượt quá số dòng của excel cũng làm được và lưu trữ để phân tích được. Về điểm này thì VBA, ADO cũng không làm được, còn công thức chỉ cần quá 10 ngàn dòng, 5 cột là hết chạy nổi.
 

vanaccex

Thành viên tiêu biểu
Tham gia
8/7/18
Bài viết
433
Được thích
278
Giới tính
Nữ
Thứ nhất: Folder nhiều file nhưng không cần mở file, thứ hai file nhiều sheet không cần tên sheet, thứ ba Sheet bao nhiêu cột, cột tên gì không cần biết.
Quan trọng hơn hết: Nếu tổng số dòng của tất cả file, tất cả sheet mà vượt quá số dòng của excel cũng làm được và lưu trữ để phân tích được. Về điểm này thì VBA, ADO cũng không làm được, còn công thức chỉ cần quá 10 ngàn dòng, 5 cột là hết chạy nổi.
Thầy @ptm0412 có thể cho Em Vân hỏi chút nếu em Vân muốn một số tên sheet cụ thể và vùng dữ liệu cụ thể ở các sheet thì có thể sửa thành thế nào ạ ?

Em Vân cảm ơn Thầy @ptm0412
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,687
Được thích
32,763
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Thầy @ptm0412 có thể cho Em Vân hỏi chút nếu em Vân muốn một số tên sheet cụ thể và vùng dữ liệu cụ thể ở các sheet thì có thể sửa thành thế nào ạ ?

Em Vân cảm ơn Thầy @ptm0412
Dùng M-Code hay bất kỳ phương pháp tổng hợp không mở file nào, cũng đòi hỏi cấu trúc dữ liệu chuẩn: Mỗi sheet chỉ 1 bảng dữ liệu, tiêu chuẩn về tiêu đề dữ liệu (không merge, tên cột không trùng, đồng nhất số lượng cột và tên cột của tất cả sheets cần tổng hợp trong tất cả cac files, ngay cả đồng nhất về chỉ số dòng chứa tiêu đề).
Nếu đạt tiêu chuẩn đó, và các sheet cần tổng hợp có tên theo 1 điểm chung nào đó, thì vẫn làm được. Ví dụ mỗi file đều có 1 (hoặc nhiều) sheet cần tổng hợp, tên của chúng là "Nhom Thao", "Nhom Ha", "Nhom Suong", "Nhom Vanaccex", ... nghĩa là bắt đầu bằng cùng 1 nhóm ký tự, hoặc kết thúc bằng cùng 1 nhóm ký tự, hoặc ví dụ tên sheet cần lấy là "Th1", "Th2", ... thì hoàn toàn có thể làm được.
Hãy tưởng tượng câu lệnh If(Left(Tên sheet, ...)) = "abc" Then
Nếu có thể dùng If như vậy mà đạt, tức là làm được. If các kiểu mà không ra thì sẽ không được.

Ví dụ chủ đề này: Tổng hợp nhiều file thành 1 file, mỗi file có nhiều sheet nhưng chỉ lấy 1 sheet "Nhom - Hà"
1631975900507.png
 
Lần chỉnh sửa cuối:

doanlong49

Thành viên mới
Tham gia
12/7/13
Bài viết
31
Được thích
13
Thứ nhất: Folder nhiều file nhưng không cần mở file, thứ hai file nhiều sheet không cần tên sheet, thứ ba Sheet bao nhiêu cột, cột tên gì không cần biết.
Quan trọng hơn hết: Nếu tổng số dòng của tất cả file, tất cả sheet mà vượt quá số dòng của excel cũng làm được và lưu trữ để phân tích được. Về điểm này thì VBA, ADO cũng không làm được, còn công thức chỉ cần quá 10 ngàn dòng, 5 cột là hết chạy nổi.
Ý tôi nói là vẫn làm trong Query mà dùng function cho dù đổi tên file, tên sheet vẫn tập hợp bình thường, mà không cần viết M code phức tạp như thế?
Function Transform file
Mã:
let
    Source = (Parameter1) => let
        Source = Excel.Workbook(Parameter1, null, true)
    in
        Source
in
    Source

M Code tập hợp

Mã:
let
    Source = Folder.Files("D:\Folder-MultiSheet"), // Thay doi duong dan phu hop   
    #"Invoke Custom Function1" = Table.AddColumn(#"Source", "Transform File", each #"Transform File"([Content])),
    #"Expanded Transform File" = Table.ExpandTableColumn(#"Invoke Custom Function1", "Transform File", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Transform File", each ([Kind] = "Table")),
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"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"}, {"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"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Data",{"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", "Item", "Name"})
in
    #"Removed Other Columns"
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,687
Được thích
32,763
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Chẳng qua Function bạn viết gọn hơn và viết tách ra ngoài, còn tôi viết Function trực tiếp bên trong Code. Với lại tôi viết để không cần biết tên cột luôn.
Khi đó 1 bộ code M này dùng cho folder bất kỳ, file bất kỳ, sheet bất kỳ, và table cấu trúc bất kỳ.
 
Lần chỉnh sửa cuối:

AnhNQT

Thành viên chính thức
Tham gia
6/11/18
Bài viết
58
Được thích
5
Giới tính
Nam
Chào bác @ptm0412 ạ,
Rất cảm ơn bài viết của bác và em đã sửa để dùng tạm 1 thời gian.
Nhưng nếu được bác có thể giúp em là chỉ lấy dữ liệu ở sheet có ký tự "p" hoặc "P" ở đầu và tên file sẽ bỏ ".xls" hoặc "xlsx" đi được không ạ.
Ngoài ra, em cần lọc những hàng có giá trị từ hàng 13 hoặc 14 trở đi ạ. Giá trị blank thì không lấy ạ (Như sheet "KQ mong muon" ạ).
Em đã thử thay bằng bài này nhưng không được ạ, mong bác sửa giúp ạ!
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,687
Được thích
32,763
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Chào bác @ptm0412 ạ,
Rất cảm ơn bài viết của bác và em đã sửa để dùng tạm 1 thời gian.
Nhưng nếu được bác có thể giúp em là chỉ lấy dữ liệu ở sheet có ký tự "p" hoặc "P" ở đầu và tên file sẽ bỏ ".xls" hoặc "xlsx" đi được không ạ.
Ngoài ra, em cần lọc những hàng có giá trị từ hàng 13 hoặc 14 trở đi ạ. Giá trị blank thì không lấy ạ (Như sheet "KQ mong muon" ạ).
Em đã thử thay bằng bài này nhưng không được ạ, mong bác sửa giúp ạ!
Điều kiện tiên quyết để sử dụng Powe query là dữ liệu phải chuẩn và cùng cấu trúc ở tất cả file, tất cả sheet. Dữ liệu của bạn vi phạm 1 đống quy tắc:
- Dữ liệu merge cell (tốn công xóa dòng trống)
- File này bắt đầu dòng 12, file kia bắt đầu dòng 13, không đồng nhất
- Có dòng tổng cộng.

Bạn phải sửa ít nhất là 2 mục 2 và 3 rồi sử dụng code:

JavaScript:
let    
    FolderName =Excel.CurrentWorkbook(){[Name="FPath"]}[Content]{0}[Column1],
    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" 
        and Text.Upper( Text.Start([Item],1)) = "P")),
        SourceData=Source1[Data],
        SheetName=Source1[Name],
        SheetNum={0..List.Count(SheetName)-1},
        DataN= List.Transform(SheetNum, (i) =>
        let
            Data0 = Table.Skip(SourceData{i},11),
            DataFName = Table.AddColumn(Table.PromoteHeaders(Data0), "File", each FileNameList{f}),
            Data1 = Table.AddColumn(DataFName, "Sheet", each SheetName{i}),
            Data2 = Table.SelectRows(Data1, each ([#"Carton No."] <> null)),
            Datai = Table.TransformColumns(Data2, {{"File", each Text.BeforeDelimiter(_, "."), type text}})
        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 = List.RemoveRange(Table.ColumnNames(DataF{0}),7,4),
    Ketqua = Table.ExpandTableColumn(List2, "Column1", ListColumns2) 
in Ketqua
 
Lần chỉnh sửa cuối:

AnhNQT

Thành viên chính thức
Tham gia
6/11/18
Bài viết
58
Được thích
5
Giới tính
Nam
Điều kiện tiên quyết để sử dụng Powe query là dữ liệu phải chuẩn và cùng cấu trúc ở tất cả file, tất cả sheet. Dữ liệu của bạn vi phạm 1 đống quy tắc:
- Dữ liệu merge cell (tốn công xóa dòng trống)
- File này bắt đầu dòng 12, file kia bắt đầu dòng 13, không đồng nhất
- Có dòng tổng cộng.

Bạn phải sửa ít nhất là 2 mục 2 và 3 rồi sử dụng code:

JavaScript:
let   
    FolderName =Excel.CurrentWorkbook(){[Name="FPath"]}[Content]{0}[Column1],
    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"
        and Text.Upper( Text.Start([Item],1)) = "P")),
        SourceData=Source1[Data],
        SheetName=Source1[Name],
        SheetNum={0..List.Count(SheetName)-1},
        DataN= List.Transform(SheetNum, (i) =>
        let
            Data0 = Table.Skip(SourceData{i},11),
            DataFName = Table.AddColumn(Table.PromoteHeaders(Data0), "File", each FileNameList{f}),
            Data1 = Table.AddColumn(DataFName, "Sheet", each SheetName{i}),
            Data2 = Table.SelectRows(Data1, each ([#"Carton No."] <> null)),
            Datai = Table.TransformColumns(Data2, {{"File", each Text.BeforeDelimiter(_, "."), type text}})
        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 = List.RemoveRange(Table.ColumnNames(DataF{0}),7,4),
    Ketqua = Table.ExpandTableColumn(List2, "Column1", ListColumns2)
in Ketqua
Em cảm ơn bác nhiều ạ! Mục 1 và 3 là chắc chắn phải vậy không sửa được do yêu cầu, mục 2 là sẽ có 2 trường hợp này ạ.
Nếu sheet có tên "PKL" thì nó sẽ bắt đầu từ dòng 12 ạ, còn sheet "packing list" sẽ bắt đầu từ dòng 13 ạ.
Được như thế này là em cảm ơn bác rất nhiều rồi ạ. Chúc bác sức khỏe.
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,687
Được thích
32,763
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Em cảm ơn bác nhiều ạ! Mục 1 và 3 là chắc chắn phải vậy không sửa được do yêu cầu, mục 2 là sẽ có 2 trường hợp này ạ.
Nếu sheet có tên "PKL" thì nó sẽ bắt đầu từ dòng 12 ạ, còn sheet "packing list" sẽ bắt đầu từ dòng 13 ạ.
Được như thế này là em cảm ơn bác rất nhiều rồi ạ. Chúc bác sức khỏe.
12 hay 13 thì thêm 1 cái if được. Còn dòng cộng thì bạn tự xóa bằng tay.
Cuối cùng, theo tôi nghĩ nếu chỉ 2 file thì làm combine bằng tay cho rồi. Code này chuyên trị dữ liệu chuẩn, không chơi dữ liệu không chuẩn. Vì dữ liệu không chuẩn khiến cho phải sửa tan nát code.
 

Cá ngừ F1

( ͡° ͜ʖ ͡°)
Thành viên BQT
Moderator
Tham gia
1/1/08
Bài viết
2,513
Được thích
3,626
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Quan hệ.. và quan hệ..
Em cảm ơn bác nhiều ạ! Mục 1 và 3 là chắc chắn phải vậy không sửa được do yêu cầu, mục 2 là sẽ có 2 trường hợp này ạ.
Nếu sheet có tên "PKL" thì nó sẽ bắt đầu từ dòng 12 ạ, còn sheet "packing list" sẽ bắt đầu từ dòng 13 ạ.
Được như thế này là em cảm ơn bác rất nhiều rồi ạ. Chúc bác sức khỏe.
Đập đi xây lại cho đẹp bạn. Là data nguồn thì phải thống nhất cấu trúc, sẽ rất nhàn trong việc xây dựng các báo cáo sau này.
 

AnhNQT

Thành viên chính thức
Tham gia
6/11/18
Bài viết
58
Được thích
5
Giới tính
Nam
12 hay 13 thì thêm 1 cái if được. Còn dòng cộng thì bạn tự xóa bằng tay.
Cuối cùng, theo tôi nghĩ nếu chỉ 2 file thì làm combine bằng tay cho rồi. Code này chuyên trị dữ liệu chuẩn, không chơi dữ liệu không chuẩn. Vì dữ liệu không chuẩn khiến cho phải sửa tan nát code.
Dạ 2 file là file mẫu ạ, còn 1 ngày là 40-50 file như loại dòng 12 cũng có, 13 cũng có. Giờ là em cần tìm cái 13 kia và xóa 1 dòng trên nó là được, như vậy là nhanh hơn nhiều so với trước e lọc xóa tay cả 3 sheet rồi ạ.
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,687
Được thích
32,763
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Dạ 2 file là file mẫu ạ, còn 1 ngày là 40-50 file như loại dòng 12 cũng có, 13 cũng có. Giờ là em cần tìm cái 13 kia và xóa 1 dòng trên nó là được, như vậy là nhanh hơn nhiều so với trước e lọc xóa tay cả 3 sheet rồi ạ.
Thay
PHP:
Data0 = Table.Skip(SourceData{i},11),
Bằng
PHP:
Data0 = Table.Skip(SourceData{i},  if  Text.Start(SheetName{i},1) = "P" then 11 else 12),
Hoặc
PHP:
Data0 = Table.Skip(SourceData{i},  if  SheetName{i} = "PKL" then 11 else 12),
 
Web KT
Top Bottom