let
FolderName ="D:\MyPham\MY BOOK\MCode-PowerQuery\B00",
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.Start([Item],4) = "Nhom")),
SourceData=Source1[Data],
SheetName=Source1[Name],
SheetNum={0..List.Count(SheetName)-1},
DataN= List.Transform(SheetNum, (i) =>
let
Data0 = Table.RemoveFirstN((SourceData{i}),1),
DataFName = Table.AddColumn(Table.PromoteHeaders(Data0), "File", each FileNameList{f}),
Datai = Table.AddColumn(DataFName, "Nhóm", 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