Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I have 4 different .csv files that sit in a folder. I want to combine all of these.
But there is a catch, the first 2 files have 4 top junk rows that need to be removed and 5 columns.
And the last 2 files have 5 top junks rows and 6 columns.
I am looking for a dynamic way of combining all my CSV files from a folder.
Expecting Result
Date | Settlement ID | closing fees | promo rebates | TDS | total |
01-01-2022 | 111222333 | 1 | 2.5 | null | 3.5 |
02-01-2022 | 111222333 | 1 | 2.5 | null | 3.5 |
03-01-2022 | 111222333 | 1 | 2.5 | null | 3.5 |
04-01-2022 | 111222333 | 1 | 2.5 | null | 3.5 |
05-01-2022 | 111222333 | 1 | 2.5 | null | 3.5 |
01-02-2022 | 222333444 | 2 | 3.5 | null | 4.5 |
02-02-2022 | 222333444 | 2 | 3.5 | null | 4.5 |
03-02-2022 | 222333444 | 2 | 3.5 | null | 4.5 |
04-02-2022 | 222333444 | 2 | 3.5 | null | 4.5 |
01-03-2022 | 444555666 | 3 | 5.5 | 1 | 9.5 |
02-03-2022 | 444555666 | 3 | 5.5 | 1 | 9.5 |
03-03-2022 | 444555666 | 3 | 5.5 | 1 | 9.5 |
01-04-2022 | 333777888 | 7.5 | 1.4 | 0.75 | 9.65 |
02-04-2022 | 333777888 | 7.5 | 1.4 | 0.75 | 9.65 |
03-04-2022 | 333777888 | 7.5 | 1.4 | 0.75 | 9.65 |
Solved! Go to Solution.
Hello, @asif999
let
Source = Folder.Files(<path_to_your_folder>),
file_names = List.Buffer(Table.AddColumn(Source, "fp", each [Folder Path] & [Name])[fp]),
fx_csv = (path as text) as table =>
let
csv = Csv.Document(File.Contents(path),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
remove_lines = Table.Skip( csv, List.PositionOf( csv[Column2], "Settlement ID")),
promote = Table.PromoteHeaders(remove_lines)
in
promote,
files = Table.Combine(List.Transform(file_names, fx_csv)),
reorder = Table.ReorderColumns(files,{"Date", "Settlement ID", "closing fees", "promo rebates", "TDS", "total"})
in
reorder
Hello, @asif999
let
Source = Folder.Files(<path_to_your_folder>),
file_names = List.Buffer(Table.AddColumn(Source, "fp", each [Folder Path] & [Name])[fp]),
fx_csv = (path as text) as table =>
let
csv = Csv.Document(File.Contents(path),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
remove_lines = Table.Skip( csv, List.PositionOf( csv[Column2], "Settlement ID")),
promote = Table.PromoteHeaders(remove_lines)
in
promote,
files = Table.Combine(List.Transform(file_names, fx_csv)),
reorder = Table.ReorderColumns(files,{"Date", "Settlement ID", "closing fees", "promo rebates", "TDS", "total"})
in
reorder
Thank @AlienSx for your answer.
I have done it this way.
let
Source = Folder.Files("B:\Excel Learning\Power Query Learning\1. Combining CSV Files with Uneven Columns & Junk Rows\CSV Files"),
Table_Cleaned = Table.AddColumn(Source, "Custom", each
let
MyTable = Csv.Document([Content]),
MyList = MyTable[Column1],
Position_Of_Date = List.PositionOf(MyList,"Date"),
My_Table_Cleaned = Table.Skip(MyTable,Position_Of_Date),
Promote_Headers = Table.PromoteHeaders(My_Table_Cleaned)
in
Promote_Headers),
Col_Names = Table.AddColumn(Table_Cleaned, "Custom.1", each Table.ColumnNames([Custom])),
Distinct_Col_Names = List.Distinct(List.Combine(Col_Names[Custom.1])),
Expaned_Table = Table.ExpandTableColumn(Table.SelectColumns(Col_Names,"Custom"),"Custom",Distinct_Col_Names)
in
Expaned_Table
I wouldn't be so confident about "Date" column in Position_Of_Date step because you have some junk data above. What if List.PositionOf finds "Date" text in there?
It won't ever find the date in junk rows. But I will consider your suggestion to remain 100% safe. I will use the second-column header instead.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
10 | |
10 | |
8 | |
7 |