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

3 Responses

1. hands says:

   18/01/2023 at 5:41 pm

   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:

   let
       FName= "D:MyPhamMY BOOKMCode-PowerQueryData4Sheet.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?

   Reply
2. hands says:

   18/01/2023 at 5:41 pm

   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ỳ.

   let
   
   FolderName ="D:MyPhamMY BOOKMCode-PowerQueryData",
       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?

   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

   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: [URL='www.giaiphapexcel.com/diendan/threads/t%E1%BB%95ng-h%E1%BB%A3p-nhi%E1%BB%81u-file-th%C3%A0nh-1-file-b%E1%BA%B1ng-power-query.156768/post-1040037']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à"
   2189

   Ý 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

   let
       Source = (Parameter1) => let
           Source = Excel.Workbook(Parameter1, null, true)
       in
           Source
   in
       Source

   M Code tập hợp

   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"

   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ỳ.

   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 [URL='www.giaiphapexcel.com/diendan/threads/t%E1%BB%95ng-h%E1%BB%A3p-nhi%E1%BB%81u-file-th%C3%A0nh-1-file-b%E1%BA%B1ng-power-query.156768/post-1040037']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:

   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.

   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.

   Reply
3. hands says:

   18/01/2023 at 5:43 pm

   Đậ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.

   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

   Data0 = Table.Skip(SourceData{i},11),

   Bằng

   Data0 = Table.Skip(SourceData{i},  if  Text.Start(SheetName{i},1) = "P" then 11 else 12),

   Hoặc

   Data0 = Table.Skip(SourceData{i},  if  SheetName{i} = "PKL" then 11 else 12),

   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 [URL='www.giaiphapexcel.com/diendan/threads/qu%C3%A0-t%E1%BA%B7ng-t%E1%BB%AB-smod-ptm0412-t%C3%A0i-li%E1%BB%87u-v%E1%BB%81-powerpivot-nh%C3%A2n-sinh-nh%E1%BA%ADt-gpe13.143641/post-927216']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.

   Reply