PQ - Filtering by filenames

[sandy666]

(question from the web)

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 folder

Table1

GDD.txt

Filename

Filename

Date

ID

Name

Company

Country

City

Sales

Text

HHT.txt

GDD

GDD

30/08/2017

16820709-4692

Sebastian B. Wynn

Class Institute

Iceland

Biez

49841

02/08/2021

KLM.txt

ZBM

GDD

27/05/2018

16530411-4530

Denton K. Cooke

Pellentesque Corporation

Saint Kitts and Nevis

Relegem

14935

08/01/2021

XRX.txt

GDD

12/09/2017

16560922-7060

Bernard E. Barrett

Nec Leo Corporation

Lesotho

Ransart

39209

12/12/2021

XXC.txt

GDD

07/07/2018

16610522-2761

Victoria N. Pace

Sagittis Industries

Poland

Kedzierzyn-KoYle

48087

22/05/2021

XYZ.txt

GDD

15/09/2018

16820520-9102

Jesse Edwards

Rutrum Associates

British Indian Ocean Territory

Chile Chico

35106

08/08/2021

ZBM.txt

GDD

24/10/2017

16841227-4543

Nita T. Lawrence

Tincidunt Orci Quis Limited

Svalbard and Jan Mayen Islands

Neustrelitz

9841

09/02/2021

ZZZ.txt

GDD

14/09/2017

16640319-2856

Galvin Bird

Ac Libero Nec LLP

Zambia

Noragugume

24617

01/02/2021

AXD.txt

GDD

12/02/2019

16230224-8493

Allen Bright

Metus Limited

Slovenia

Townsville

18105

02/08/2021

AZD.txt

GDD

08/12/2018

16261203-9533

Bo Sweeney

Ipsum Suspendisse Non Consulting

Bahamas

Ichalkaranji

6999

08/01/2021

GDD

13/04/2017

16290911-4387

Casey Odom

Tellus Eu Limited

Paraguay

Wortel

31503

12/12/2021

ZBM

12/10/2017

16221206-7421

Thomas Salas

Purus In Consulting

Dominica

Rishra

31943

01/02/2021

ZBM

24/12/2018

16800603-3040

Joan Steele

Nulla Facilisi Sed Consulting

Mexico

Siquirres

33836

02/08/2021

ZBM

04/10/2017

16111012-0647

Hillary Y. Mann

Interdum Curabitur Dictum LLC

Seychelles

Augusta

10307

08/01/2021

ZBM

27/02/2019

16990503-3412

Melissa H. Ellis

Proin Dolor Inc.

Bulgaria

Saint-Louis

13906

12/12/2021

ZBM

15/12/2017

16260324-2443

Bert Bradford

Eu Augue Porttitor PC

Andorra

Kinrooi

16306

22/05/2021

ZBM

26/11/2018

16321124-5133

Hiram Myers

Pretium Et Institute

Burundi

Saint-Brieuc

31502

08/08/2021

ZBM

12/06/2017

16810311-3422

Mohammad Mcconnell

Aliquet Diam Incorporated

Netherlands

Gorakhpur

31492

09/02/2021

ZBM

27/01/2019

16720914-5452

Tatiana Gallegos

Lorem Consulting

Saint Barthélemy

Madison

11058

01/02/2021

ZBM

08/05/2018

16630509-1271

Maxwell Y. Galloway

Curabitur Consequat Foundation

United Kingdom (Great Britain)

Landeck

30513

02/08/2021

ZBM

05/04/2019

16790820-2315

Blythe Schneider

Parturient Limited

Tuvalu

Oyace

32994

08/01/2021

STEPS:
1.
fromfolder01.png
2.
fromfolder02.png
3.
textbeforedelimiter.png

// 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
choosecols.png

// 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
Filtering.zip