cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ikeumlaut Advocate I
Advocate I

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

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 ValueCategory ACategory CCategory D........Category Z
1.1.2014 100 504010   
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!

3 REPLIES 3
BhaveshPatel Community Champion
Community Champion

Re: Import multiple csv files that have different rows and each requires a custom column before merg

@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

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Ikeumlaut Advocate I
Advocate I

Re: Import multiple csv files that have different rows and each requires a custom column before merg

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!

Microsoft v-huizhn-msft
Microsoft

Re: Import multiple csv files that have different rows and each requires a custom column before merg

Hi @Ikeumlaut,

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

Best Regards,
Angelia

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors