5 Attachment(s)
PQ - Filtering by filenames
(question from the web)
Quote:
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.
Attachment 3521
2.
Attachment 3522
3.
Attachment 3523
Code:
// 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
Attachment 3525
Code:
// 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
Attachment 3520
