Results 1 to 9 of 9

Thread: How to filter multiple files for latest version of each file
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2014
    Location
    Arkansas
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to filter multiple files for latest version of each file

    I am using Power Query in Excel for the first time. Got query to work fine but have too much unneeded data.
    Query combines 1000+ files. Each file has ~150 rows of data. Files are named using serial number (SN) and Revision letter for each file.
    There are multiple files of different revision levels for each serial number. The file name is shown to left of each row of data.

    I only need the rows of data for the latest/highest revision of each serial number. That would equal ~ 33 rows for each serial number. This would give me ~11,000 rows of data instead of the unneeded 540,000+ rows.

    So question 1 is can this be done in power query?

    If yes then question 2 is how is it done? Need specific details on how to do it as I am new at this in PQ.

    Here is an example of 2 Revs for SN 133. I only need the rows that have Rev B to be displayed.

    Source.Name OPTION OPTION SELECTED
    APM Form for F2X DN133 SN133 Rev A.xls Aircraft Model F-2000
    APM Form for F2X DN133 SN133 Rev A.xls Winglets Installed Yes
    APM Form for F2X DN133 SN133 Rev A.xls ABOC Installed
    APM Form for F2X DN133 SN133 Rev A.xls BASC Installed 700
    APM Form for F2X DN133 SN133 Rev A.xls Battery Voltage Drop Suppression No
    APM Form for F2X DN133 SN133 Rev B.xls Aircraft Model F-2000
    APM Form for F2X DN133 SN133 Rev B.xls Winglets Installed Yes
    APM Form for F2X DN133 SN133 Rev B.xls ABOC Installed
    APM Form for F2X DN133 SN133 Rev B.xls BASC Installed 700
    APM Form for F2X DN133 SN133 Rev B.xls Battery Voltage Drop Suppression No

  2. #2
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter multiple files for latest version of each file

    Here is one way, it assume the serial number is 3 items from the end

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        columns.Type = Table.TransformColumnTypes(Source,{{"Source.Name", type text}, {"OPTION", type text}, {"OPTION SELECTED", type any}}),
        serial.Singles = Table.Distinct(columns.Type, {"Source.Name"}),
        source.Split = Table.AddColumn(serial.Singles, "Source.Split", each Text.Split([Source.Name]," ")),
        serial.Exttract = Table.AddColumn(source.Split, "Serial", each [Source.Split]{List.Count([Source.Split])-3}),
        serial.rev.Extract = Table.AddColumn(serial.Exttract, "SerialRev", each [Source.Split]{List.Count([Source.Split])-3}&List.Last([Source.Split])),
        columns.Stripdown = Table.RemoveColumns(serial.rev.Extract,{"OPTION", "OPTION SELECTED", "Source.Split"}),
        index0.Add = Table.AddIndexColumn(columns.Stripdown, "Index", 0, 1),
        index1.Add = Table.AddIndexColumn(index0.Add, "Index.1", 1, 1),
        next.Join = Table.NestedJoin(index1.Add,{"Index.1"},index1.Add,{"Index"},"Added Index1",JoinKind.LeftOuter),
        columns.Stripdown = Table.RemoveColumns(next.Join,{"SerialRev", "Index", "Index.1"}),
        next.Extpand = Table.ExpandTableColumn(columns.Stripdown, "Added Index1", {"Serial"}, {"Next.Serial"}),
        tokeep.Test = Table.AddColumn(next.Extpand, "ToKeep", each if [Serial]<>[Next.Serial] then true else false),
        tokeep.Filter = Table.SelectRows(tokeep.Test, each ([ToKeep] = true)),
        allrows.Retrieve = Table.NestedJoin(columns.Type,{"Source.Name"},tokeep.Filter,{"Source.Name"},"Filtered Rows",JoinKind.Inner),
        table.Remove = Table.RemoveColumns(allrows.Retrieve,{"Filtered Rows"})
    in
        table.Remove
    Last edited by theBardd; Apr 12th, 2019 at 07:10 PM.

  3. #3
    New Member
    Join Date
    Nov 2014
    Location
    Arkansas
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter multiple files for latest version of each file

    When your code was used I got:
    Expression.SyntaxError: Token Eof expected.

    In list of workbook queries I have a warning of Download did not complete.

    Here is my original code that shows all revs of each line item:

    let
    Source = Folder.Files("J:\Dept 240\APM\EASy II\Requests\Processed Requests\F2X"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from F2X", each #"Transform File from F2X"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from F2X"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from F2X", Table.ColumnNames(#"Transform File from F2X"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", Int64.Type}, {"Column10", type text}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column2", "OPTION"}, {"Column5", "OPTION SELECTED"}})
    in
    #"Renamed Columns"

  4. #4
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter multiple files for latest version of each file

    I didn't get that error, but I did get a duplicate step-name.

    This version works for me but it is just one file, not a folder like yours, so it needs modifying.

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        columns.Type = Table.TransformColumnTypes(Source,{{"Source.Name", type text}, {"OPTION", type text}, {"OPTION SELECTED", type any}}),
        serial.Singles = Table.Distinct(columns.Type, {"Source.Name"}),
        source.Split = Table.AddColumn(serial.Singles, "Source.Split", each Text.Split([Source.Name]," ")),
        serial.Exttract = Table.AddColumn(source.Split, "Serial", each [Source.Split]{List.Count([Source.Split])-3}),
        serial.rev.Extract = Table.AddColumn(serial.Exttract, "SerialRev", each [Source.Split]{List.Count([Source.Split])-3}&List.Last([Source.Split])),
        columns.Stripdown = Table.RemoveColumns(serial.rev.Extract,{"OPTION", "OPTION SELECTED", "Source.Split"}),
        index0.Add = Table.AddIndexColumn(columns.Stripdown, "Index", 0, 1),
        index1.Add = Table.AddIndexColumn(index0.Add, "Index.1", 1, 1),
        next.Join = Table.NestedJoin(index1.Add,{"Index.1"},index1.Add,{"Index"},"Added Index1",JoinKind.LeftOuter),
        columns2.Stripdown = Table.RemoveColumns(next.Join,{"SerialRev", "Index", "Index.1"}),
        next.Extpand = Table.ExpandTableColumn(columns2.Stripdown, "Added Index1", {"Serial"}, {"Next.Serial"}),
        tokeep.Test = Table.AddColumn(next.Extpand, "ToKeep", each if [Serial]<>[Next.Serial] then true else false),
        tokeep.Filter = Table.SelectRows(tokeep.Test, each ([ToKeep] = true)),
        allrows.Retrieve = Table.NestedJoin(columns.Type,{"Source.Name"},tokeep.Filter,{"Source.Name"},"Filtered Rows",JoinKind.Inner),
        table.Remove = Table.RemoveColumns(allrows.Retrieve,{"Filtered Rows"})
    in
        table.Remove

  5. #5
    New Member
    Join Date
    Nov 2014
    Location
    Arkansas
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter multiple files for latest version of each file

    Now I can see the steps under Applied Steps. But get Expression.Error: We couldn't find an Excel table named 'Table3'.Details:
    Table3

    What is Table 3? looks like it would be a file name. I put in name of my file but it did not work.

  6. #6
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter multiple files for latest version of each file

    As I said, my code is for one file, and that file is current workbook. I am querying a table called Table3, you will need to change it to get all of your files in first before you use the transforming code I give.

  7. #7
    New Member
    Join Date
    Nov 2014
    Location
    Arkansas
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter multiple files for latest version of each file

    As I'm new to power query I'm not sure how to get all my files in. I have an excel worksheet that contains all the files. Worksheet has total of 14 columns but I only need to use the 3 I made sample from. Do I enter the column (table) names? You already have the names of my 3 columns in what you sent me.

  8. #8
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter multiple files for latest version of each file

    Doesn't this part of the code that you supplied do that, grabbing all the files in the folder? I couldn't test it as I don't have any files.
    Code:
    let
        Source = Folder.Files("J:\Dept 240\APM\EASy II\Requests\Processed Requests\F2X"),
        #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
        #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from F2X", each #"Transform File from F2X"([Content])),
        #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
        #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from F2X"}),
        #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from F2X", Table.ColumnNames(#"Transform File from F2X"(#"Sample File"))),
    Append my code starting at columns.TYpe, change my first row refence to the previous step, and tweak it all.

  9. #9
    New Member
    Join Date
    Nov 2014
    Location
    Arkansas
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter multiple files for latest version of each file

    Sorry for not replying sooner. Been out sick a few days. Looks like I should add your code to mine. But I don't know where to add it. Nor do I know exactly what/how to change your first row to previous step - -change to what? Then how/what do I tweak it all? Below is what I currently have in Advanced Editor using your code and mine.

    let
    Source = Folder.Files("J:\Dept 240\APM\EASy II\Requests\Processed Requests\F2X"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from F2X", each #"Transform File from F2X"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from F2X"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from F2X", Table.ColumnNames(#"Transform File from F2X"(#"Sample File"))),






    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", Int64.Type}, {"Column10", type text}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column2", "OPTION"}, {"Column5", "OPTION SELECTED"}})
    in
    #"Renamed Columns"


    let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    columns.Type = Table.TransformColumnTypes(Source,{{"Source.Name", type text}, {"OPTION", type text}, {"OPTION SELECTED", type any}}),
    serial.Singles = Table.Distinct(columns.Type, {"Source.Name"}),
    source.Split = Table.AddColumn(serial.Singles, "Source.Split", each Text.Split([Source.Name]," ")),
    serial.Exttract = Table.AddColumn(source.Split, "Serial", each [Source.Split]{List.Count([Source.Split])-3}),
    serial.rev.Extract = Table.AddColumn(serial.Exttract, "SerialRev", each [Source.Split]{List.Count([Source.Split])-3}&List.Last([Source.Split])),
    columns.Stripdown = Table.RemoveColumns(serial.rev.Extract,{"OPTION", "OPTION SELECTED", "Source.Split"}),
    index0.Add = Table.AddIndexColumn(columns.Stripdown, "Index", 0, 1),
    index1.Add = Table.AddIndexColumn(index0.Add, "Index.1", 1, 1),
    next.Join = Table.NestedJoin(index1.Add,{"Index.1"},index1.Add,{"Index"},"Added Index1",JoinKind.LeftOuter),
    columns2.Stripdown = Table.RemoveColumns(next.Join,{"SerialRev", "Index", "Index.1"}),
    next.Extpand = Table.ExpandTableColumn(columns2.Stripdown, "Added Index1", {"Serial"}, {"Next.Serial"}),
    tokeep.Test = Table.AddColumn(next.Extpand, "ToKeep", each if [Serial]<>[Next.Serial] then true else false),
    tokeep.Filter = Table.SelectRows(tokeep.Test, each ([ToKeep] = true)),
    allrows.Retrieve = Table.NestedJoin(columns.Type,{"Source.Name"},tokeep.Filter,{"Source.Name"},"Filtered Rows",JoinKind.Inner),
    table.Remove = Table.RemoveColumns(allrows.Retrieve,{"Filtered Rows"})
    in
    table.Remove

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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