PQ - Filtering by filenames

[sandy666]

(question from the web (https://www.mrexcel.com/board/threads/filter-table-selectrows-from-cell-rang-in-sheet.1161493/))

I Imported some data from folder. folder has 100 txt file. I need select txt file based on names listed in column2 at sheet2

This example contain 10 txt files.


List of files in a folderTable1

GDD.txtFilenameFilenameDateIDNameCompanyCountryCit ySalesText

HHT.txtGDDGDD
30/08/201716820709-4692Sebastian B. WynnClass InstituteIcelandBiez
49841
02/08/2021

KLM.txtZBMGDD
27/05/201816530411-4530Denton K. CookePellentesque CorporationSaint Kitts and NevisRelegem
14935
08/01/2021

XRX.txtGDD
12/09/201716560922-7060Bernard E. BarrettNec Leo CorporationLesothoRansart
39209
12/12/2021

XXC.txtGDD
07/07/201816610522-2761Victoria N. PaceSagittis IndustriesPolandKedzierzyn-KoYle
48087
22/05/2021

XYZ.txtGDD
15/09/201816820520-9102Jesse EdwardsRutrum AssociatesBritish Indian Ocean TerritoryChile Chico
35106
08/08/2021

ZBM.txtGDD
24/10/201716841227-4543Nita T. LawrenceTincidunt Orci Quis LimitedSvalbard and Jan Mayen IslandsNeustrelitz
9841
09/02/2021

ZZZ.txtGDD
14/09/201716640319-2856Galvin BirdAc Libero Nec LLPZambiaNoragugume
24617
01/02/2021

AXD.txtGDD
12/02/201916230224-8493Allen BrightMetus LimitedSloveniaTownsville
18105
02/08/2021

AZD.txtGDD
08/12/201816261203-9533Bo SweeneyIpsum Suspendisse Non ConsultingBahamasIchalkaranji
6999
08/01/2021

GDD
13/04/201716290911-4387Casey OdomTellus Eu LimitedParaguayWortel
31503
12/12/2021

ZBM
12/10/201716221206-7421Thomas SalasPurus In ConsultingDominicaRishra
31943
01/02/2021

ZBM
24/12/201816800603-3040Joan SteeleNulla Facilisi Sed ConsultingMexicoSiquirres
33836
02/08/2021

ZBM
04/10/201716111012-0647Hillary Y. MannInterdum Curabitur Dictum LLCSeychellesAugusta
10307
08/01/2021

ZBM
27/02/201916990503-3412Melissa H. EllisProin Dolor Inc.BulgariaSaint-Louis
13906
12/12/2021

ZBM
15/12/201716260324-2443Bert BradfordEu Augue Porttitor PCAndorraKinrooi
16306
22/05/2021

ZBM
26/11/201816321124-5133Hiram MyersPretium Et InstituteBurundiSaint-Brieuc
31502
08/08/2021

ZBM
12/06/201716810311-3422Mohammad McconnellAliquet Diam IncorporatedNetherlandsGorakhpur
31492
09/02/2021

ZBM
27/01/201916720914-5452Tatiana GallegosLorem ConsultingSaint BarthélemyMadison
11058
01/02/2021

ZBM
08/05/201816630509-1271Maxwell Y. GallowayCurabitur Consequat FoundationUnited Kingdom (Great Britain)Landeck
30513
02/08/2021

ZBM
05/04/201916790820-2315Blythe SchneiderParturient LimitedTuvaluOyace
32994
08/01/2021

STEPS:
1.
3521
2.
3522
3.
3523


// Files
let
Source = Folder.Files("D:\test\Filtering\Files"),
TBD = Table.TransformColumns(Source, {{"Name", each Text.BeforeDelimiter(_, "."), type text}})
in
TBD

4. modify 5th steps in Merge1
3525


// Merge1
let
Source = Table.NestedJoin(Table1,{"Filename"},Files,{"Name"},"Files",JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Source, "Files", {"Content"}, {"Content"}),
FilterHidden = Table.SelectRows(Expand, each [Attributes]?[Hidden]? <> true),
Invoke = Table.AddColumn(FilterHidden, "Transform File from Merge1", each #"Transform File from Merge1"([Content])),
ROC = Table.SelectColumns(Invoke,{"Filename", "Transform File from Merge1"}),
ExpandTable = Table.ExpandTableColumn(ROC, "Transform File from Merge1", Table.ColumnNames(#"Transform File from Merge1"(#"Sample File"))),
Type = Table.TransformColumnTypes(ExpandTable,{{"Date", type date}, {"ID", type text}, {"Name", type text}, {"Company", type text}, {"Country", type text}, {"City", type text}, {"Sales", Int64.Type}, {"Text", type date}})
in
Type

working example attached
3520