Import multiple csv files that have different rows and each requires a custom column before merging

[Ikeumlaut]

Hey,

I have over 100 csv files that I need to work with using Power BI. The problem is that many of the files have different amount of rows and different row titles. Additionally, each file contains information about the data the I need to transform into a new column.

I've been trying to use power query and I can solve my problem partly but I'm stuck with my beginner skills. Please help!

Here is an example of the table contained in the csv files:

---------------------------------------------------------------------------------------
# Time Period : 2014/01/01 - 2016/01/01
# Region : 1
#
#
---------------------------------------------------------------------------------------
Date		Total Value	Category A	Category C	Category D	....	....	Category Z
1.1.2014		100	50	40	10
2.1.2014		50		50
3.1.2014		0
...
...
..
...
1.1.2015

All the rows and columns match in each file expect that I have different Categories in different files. As I need the Region info I need to add that to each file as a new column before joining them. I have managed to do that in power query with the following steps:

let
    Source = Csv.Document(File.Contents("C:\File X.csv"),[Delimiter=",", Columns=65, Encoding=932, QuoteStyle=QuoteStyle.None]),
    #"Added Custom" = Table.AddColumn(Source, "Region", each Source[Column1]{2}),
    #"Removed Top Rows" = Table.Skip(#"Added Custom",6),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Region"}}),
in
    #"Renamed Columns"

So how can I apply these steps to each file and then combine all of them into one table? I could do it all manually but it would take forever. I think I need to use import folder functions but cannot get it to work and I have no idea how to add the steps above.

I've tried to add the steps into this function, but cannot get it to work.

let
    Source = Folder.Files("C:\csv files"),
    Tables = List.Transform(Source[Content], each Table.PromoteHeaders(Csv.Document(_,null,null,null,1252))),
    SingleTable = Table.Combine(Tables)
in
    SingleTable

Thank you!

[BhaveshPatel]

@Ikeumlaut

You should try using R to combine those files as shown in the below videos:

https://www.youtube.com/watch?v=IXcmqflUoTI

https://www.youtube.com/watch?v=3BwXS-ug26w

Best Regards,

Bhavesh

[Ikeumlaut]

Hey,

that would be straight forward if the files would not need any modifications. How do I automatically do the same editing operation to each file before merging them with R? For each file I need to do these edits:

#"Added Custom" = Table.AddColumn(Source, "Region", each Source[Column1]{2}),
#"Removed Top Rows" = Table.Skip(#"Added Custom",6),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Region"}}),

Are they possible with R?

Thanks!

[v-huizhn-msft]

Hi @Ikeumlaut,

You can Post the issue to R forum, where you can get more professional support.

Best Regards,
Angelia