Results 1 to 1 of 1

Thread: PQ - Filtering by filenames

  1. #1
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    236
    Rep Power
    7

    Cool PQ - Filtering by filenames

    (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

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

    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
    Filtering.zip
    Last edited by sandy666; 02-28-2021 at 01:19 AM.
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

Similar Threads

  1. Subtotal with Filtering
    By Sakalansalex in forum Excel Help
    Replies: 2
    Last Post: 10-25-2017, 02:54 PM
  2. Replies: 6
    Last Post: 04-15-2014, 03:58 PM
  3. Drop-down list (with filtering)
    By mahmoud-lee in forum Excel Help
    Replies: 2
    Last Post: 02-22-2014, 12:14 AM
  4. Replies: 5
    Last Post: 12-05-2012, 03:01 AM
  5. Replies: 2
    Last Post: 09-24-2012, 06:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •