Gộp nhiều file, nhiều sheet, khác range bằng PowerQuery.

[htin1997]

Xin chào GPE,

Như tiêu đề, em cần gộp các file lại với nhau, mỗi file có 3 sheet, mỗi sheet có 1 range (không phải dạng bảng). Tuy nhiên, các range có các cột có cả giống nhau và khác nhau.
Kết quả cần là:
1. Ghép các sheet lại theo:



2. Sau đó lại gộp các file với cách gộp như gộp sheet.
Em làm được bước 1 nhưng làm mỗi lần chỉ đươc 1 file. Nhờ GPE chỉ cách chọn nhiều file(hoặc folder) và query 1 lần ra kết quả ạ.
Em xin cảm ơn.

 

[ptm0412]

Như tiêu đề, em cần gộp các file lại với nhau, mỗi file có 3 sheet, mỗi sheet có 1 range (không phải dạng bảng).

Không biết bước 1 bạn làm cách nào, File Data1 có số lượng cột ít hơn file Data2 (đã gộp hết sheet). Tôi làm cách đơn giản là lấy hết 10 cột của cả 2 file là 1 bảng column list chuẩn bắt tất cả sheet, tất cả file phải theo.



Code sẽ là:

let
    FolderName ="D:\OneDrive\MyBooks\MCode-PowerQuery\Invoice",
    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}),
            Data1 = Table.AddColumn(Table.PromoteHeaders(Data0), "FileNSheet", each Text.Replace(FileNameList{f},".xlsx","") & "-" & SheetName{i})   
        in Data1),
        List1 =  Table.FromList(DataN, Splitter.SplitByNothing()),
        ListColumns = Excel.CurrentWorkbook(){[Name="ColList"]}[Content][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),
    Ketqua1 = Table.SelectRows(Ketqua, each ([code] <> null))

in Ketqua1

 

[htin1997]

Dạ cám ơn thầy, giờ con chỉ cần sửa đường dẫn thư mục và refresh là có dữ liệu.

Không biết bước 1 bạn làm cách nào

Dạ đây là cách con làm nè thầy, con không biết code nên là làm thủ công.
Đây là file con ghi bằng Step Recorder.

 

[ptm0412]

Dạ đây là cách con làm nè thầy, con không biết code nên là làm thủ công.
Đây là file con ghi bằng Step Recorder.

Đó là cách append. Mỗi sheet tạo 1 query con rồi append chúng lại với nhau.
Ý chính tôi nói ở trên là giả sử file thứ nhất có 3 sheet, file thứ hai có 4 sheet, sẽ phải có 7 query con và append chúng lại. Khi xuất hiện file thứ 3 sẽ phải làm lại các bước.
--------
Một cách làm khác là get Data from folder sẽ tự động lấy tất cả file trong folder nhưng mỗi file chỉ lấy được 1 sheet, và phải chọn 1 file làm mẫu (sample). Nếu file này chỉ có 4 cột thì tất cả file đều chỉ lấy 4 cột. Chọn khéo thì lấy file nhiều cột nhất làm sample (giả sử 8 cột). Khi xuất hiện file thứ 3 nó sẽ tự động gộp vào nhưng vẫn chỉ 8 cột. Nếu file thứ 3 10 cột thì bị mất 2.
Do đó tôi mới tạo 1 danh sách cột chuẩn để ép tất cả file, tất cả sheet phải theo chuẩn.

Bạn tìm đọc tài liệu tôi viết trên diễn đàn này rất chi tiết các việc append, merge, ... các kiểu. Nâng cao như bài này là các chủ đề riêng để thực hành.