Append Files Dinamically

[alexanderg]

Hello Everybody... I want to append multiple files located into a folder but I have to applie some transformations to these files, the transformation is similar to each file. So I want every file gets the same transformation and all appended dynamically, meaning that for every new file in the folder, the model must append automatically the content.

Thanks for your comments...

[pqian]

I should also mention that if you aren't looking to apply complex transformations to each of the files before they are combined, then it's a much easier process, simply use the Combine button

Once you click this, PowerQuery automatically combine the binaries then open them according. You will end up with one table with all the contents concatenated. You can then apply the transformations to EACH ROW of the resultant table.

There are both reasons to apply transformations before or after the concatentation. You should experiment with these tools based on your needs.

[greggyb]

You can do this by using Power BI's import Folder option in Power Query.

[Greg_Deckler]

@alexanderg - If I am understanding what you want to do, you want to use the Folder data source.

[pqian · Accepted solution]

@alexanderg, this should be fairly easy to do in PowerBI desktop. But you need a bit of knowledge of M (https://msdn.microsoft.com/en-us/library/mt211003.aspx)

I'll break the process down in steps

1. Get the list of files from the folder

You can use the New Source->From Folder function to do this. Click EDIT once you see the list of files

2. (Optional) Filter the list of files down to the ones you want. Typically you can filter by extension, etc. 

3. (Optional) Sort the list of files according to your needs. This will guarantee a stable output 

4. The first column of the table is a list of "Binary" items. These represent the content of the files. Since you want to apply similar transforms to all of these items, you need to write your transform in a separate query, and then turn it into a function that takes ONE binary input. Click New Source->Blank Query and enter the following into the formula bar:

= (input) => Table.FromColumns({Lines.FromBinary(input)})

Rename this query to something meaningful, like "Transform".

5. You now have a base query for your transforms. You can tailor this query to your needs. One trick to author this if you aren't very familier with M is to go back to the query with the list of Binaries, click on one of them to open it, and use that as a sample to work with. Once you are done with the sample, you can goto the Advance Editor and copy the added steps out. These can be turned into a function using the lambda expression above. One important thing to note: your custom transform must return a TABLE, otherwise they won't work later for concatenation.

6. Now you can invoke the function on each of the binaries. From the list of binaries, right click on the column header for the binary column, and then click "Drill Down". You should end up with just a list. Then you want to invoke the list.transform function to apply your custom function to them. Change the formula text to:

= List.Transform(#"Filtered Rows"[Content], each Transform(_)}))

Note that in this example I had only filtered down my files, so #"Filtered Rows"[Content] is the formula text for the drilled down step. Transform is the query name in 4.

7. Combine the list into the final result, Here you can simply use Table.Combine

= Table.Combine(Content)

Content is the step name in 6.

That should be it!