cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jinji Frequent Visitor
Frequent Visitor

How to merge multiple CSV with different format?

I have daily spending data in CSV files in several formats.

How can I extract the columns I need, and merge all of them for the power BI analysis?

 

We have 3 purchasing systems, each system generates daily spending file in different formats.

For example, 

CSV1: Supplier, Product, Price, Quantity, Amount, Date, ...

CSV2: Date, Product, Price, Quantity, Amount, Supplier, Category, Project Code,...

CSV3: Date, Product, Price, Quantity, Amount, Department,...

 

We want to extract Date, Product and Amount from each file, and merge them into 1 file, so that it can be used for analysis and available for refreshin Power BI. 

 

Can anyone tell me how to do this?

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
teylyn Regular Visitor
Regular Visitor

Re: How to merge multiple CSV with different format?

Hello,

 

create a query for the first CSV file, edit and remove the columns for Supplier, Price and Quantity and move the Date column to the left. 

 

Create a query for the second CSV file, remove the columns you don't need.

 

Create a query for the third CSV file, remove the columns you don't need. Then append the first query and then append the second query.

 

Close and apply. 

2 REPLIES 2
teylyn Regular Visitor
Regular Visitor

Re: How to merge multiple CSV with different format?

Hello,

 

create a query for the first CSV file, edit and remove the columns for Supplier, Price and Quantity and move the Date column to the left. 

 

Create a query for the second CSV file, remove the columns you don't need.

 

Create a query for the third CSV file, remove the columns you don't need. Then append the first query and then append the second query.

 

Close and apply. 

Jinji Frequent Visitor
Frequent Visitor

Re: How to merge multiple CSV with different format?

Thank you very much!!  Just tried and it worked!!