Append CSVs with different colomun order, names and quantity

tamiri
Posts: 3
Joined: Tue Feb 07, 2017 4:13 pm

Append CSVs with different colomun order, names and quantity

Postby tamiri » Tue Feb 07, 2017 6:04 pm

Hello,
I have 3 CSVs files wich I need to append.
Each should represent the same instances for 3 different years.
The problem is that the CSVs contain columns that are not in the same order, nor name nor amount of columns.
I need to analys the data and for this purpose I need to append those CSVs (and maybe more files as the years go by).
Is there any smart way to do it in PowerPivot/Query?
(this a link to three sample files: https://drive.google.com/drive/folders/0Bzz1wn6UL_b6Ykd6WVpCS2phMGs?usp=sharing)

Thank you,
Tamir

MattAllington
Posts: 845
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Append CSVs with different colomun order, names and quantity

Postby MattAllington » Tue Feb 14, 2017 4:41 pm

Well if you want to append them, there has to be some structure. If the columns are in any order and any name, the query has no automatic way of knowing what to put where. If they have the same name but a different order, then it will work just fine. If you have the same order but different names, you can use a relative column reference.

Maybe the easiest way is to load the 3 files individually (create connection only) rename the columns and then combine them. Can this work?
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

tamiri
Posts: 3
Joined: Tue Feb 07, 2017 4:13 pm

Re: Append CSVs with different colomun order, names and quantity

Postby tamiri » Tue Feb 14, 2017 4:57 pm

I will try it.
Thank you for the reply,
Tamir


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest