none
Dynamic Binary.Combine RRS feed

  • Question

  • Hi,

    I have a table below for the user to input. 

    List
    2014 DEC 234.csv
    2014 JUN 234.csv
    2014 SEP 234.csv

    If there are three items in the table, I will do the Binary.Combine for those three files.

    #"Filtered Rows" = Table.SelectRows(Source, each [Name] = "2014 DEC 234.csv" or [Name] = "2014 JUN 234.csv" or [Name] = "2014 SEP 234.csv"),

    #"Combined Binaries" = Binary.Combine(#"Filtered Rows"[Content]),

    If there are only two items in the table, I will combine those two.

    List
    2014 DEC 234.csv
    2014 JUN 234.csv

    #"Filtered Rows" = Table.SelectRows(Source, each [Name] = "2014 DEC 234.csv" or [Name] = "2014 JUN 234.csv"),

    #"Combined Binaries" = Binary.Combine(#"Filtered Rows"[Content]),

    How to achieve this dynamic feature?

    Thanks,

    Tuesday, November 3, 2015 4:14 PM

Answers

  • I find a solution. Consider the input table as table1, and another table which includes all files as table2.

    Use Table.Join(table1,xx,table,xx,..) to replace #"Filtered Rows" = Table.SelectRows(Source, each [Name] = "2014 DEC 234.csv" or [Name] = "2014 JUN 234.csv"),

    The result will be the same.


    Tuesday, November 3, 2015 5:53 PM

All replies

  • Hello bjzk,

    May be you can filter your list like this:

    #"Filtered Rows" = Table.SelectRows(Source, each Text.End([Name],4) = ".csv")


    Maxim Zelensky Excel Inside

    Tuesday, November 3, 2015 4:42 PM
  • Hi Maxim,

    Thanks for the reply. This is just an example. There are total more than 20 files (end with 234 or S01 or other) and the user can choose whatever number of files they want to compare (usually 4 to 6). The table can be 

    2014 DEC 234.csv

    2014 JUN S01.csv

    2014 SEP SXX.csv




    Tuesday, November 3, 2015 5:13 PM
  • I find a solution. Consider the input table as table1, and another table which includes all files as table2.

    Use Table.Join(table1,xx,table,xx,..) to replace #"Filtered Rows" = Table.SelectRows(Source, each [Name] = "2014 DEC 234.csv" or [Name] = "2014 JUN 234.csv"),

    The result will be the same.


    Tuesday, November 3, 2015 5:53 PM
  • Hi bjzk

    But wait, you have a list of already selected (by user) files, and all of them has ".csv" extension, right? So when you perform filtering in the way I suggested, you just get full list of this files, doesn't matter if it has one, two or six files in it.

    The main idea that when you apply a filter to a list, you can get the same list if any of list items fulfill criteria of "=", or any of list items fulfill criteria of "<>"

    Or this list is not a list of user-selected files, but total list of files, and user somehow need to enter file name in formula? I don't sure if I catch your idea


    Maxim Zelensky Excel Inside

    Tuesday, November 3, 2015 6:01 PM
  • Hi Maxim,

    Maybe it will be easier to understand it using one example.

    Below is the part of the table which contains all the files that need to be filtered (name it table 2).


    Name
    2014 DEC (pre S_G restatement).csv
    2014 DEC.csv
    2014 JUN.csv
    2014 SEP.csv
    2015 JUN 234.csv
    2015 Jun S01 Demo.csv
    2015 Jun S01.csv
    2015 SEP 234.csv
    2015 SEP S01.csv

    And below is the input table (name it table 1)

    Data File
    2015 SEP S01.csv

    The user input whatever files that they want to check in table1 and the query will grab the data from those files.

    This is why I use join to solve the problem.

    The codes are below:

        FileNameTable= Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        NotNull = Table.SelectRows(FileNameTable, each ([Data File] <> null)),
        Source = Excel.CurrentWorkbook(){[Name="DataSource"]}[Content]{0}[Data Source],// File Path
        Files= Folder.Files(Source),
        Selectfile=Table.Join(Files,{"Name"},NotNull,{"Data File"},JoinKind.Inner),
        Combined = Binary.Combine(Selectfile[Content]),
        CSV= Csv.Document(Combined,[Delimiter=",",Encoding=65001]),
        PromotedHeaders = Table.PromoteHeaders(CSV)

    Hope this is clear.

    Thanks

                                                    

    Tuesday, November 3, 2015 6:57 PM
  • ok, now its clear - I missed that part when you got files list from folder.


    Maxim Zelensky Excel Inside

    Tuesday, November 3, 2015 10:21 PM