Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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!
Hi @Ikeumlaut,
You can Post the issue to R forum, where you can get more professional support.
Best Regards,
Angelia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |