# Dynamic Binary.Combine

• ### 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

• 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]),

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