doing some conversion of a csv table in Powerpivot and could use some help!

[harperjo]

I have a csv table that I have to do some converting on to be used as an import for a database load. An example would be similar to the table below. of course I have 225 attributes in the table to deal with.

ID	description	type	attrib1	attrib2	attrib3	attrib4
1	EXFAN223	fan01	1750		5blade	belt
2	SUPFAN01	fan02	2300	25"	squirrel	direct
3	EXFAN109	fan01	1750		3blade	belt

<tbody>
</tbody>

the conversion question is this.....I have to take this csv above and turn it into the one below.

ID	description	type	attribute	value
1	EXFAN223	fan01	attrib1	1750
1	EXFAN223	fan01	attrib3	5blade
1	EXFAN223	fan01	attrib4	belt
2	SUPFAN01	fan02	attrib1	2300
2	SUPFAN01	fan02	attrib2	25"
2	SUPFAN01	fan02	attrib3	squirrel
2	SUPFAN01	fan02	attrib4	direct
3	EXFAN109	fan01	attrib1	1750
3	EXFAN109	fan01	attrib3	3blade
3	EXFAN109	fan01	attrib4	belt

<tbody>
</tbody>

the attributes headers and the values get transposed (if they exist)

any ideas on how to get this working?

 

[ImkeF]

In Power Query you find a function called Unpivot.OtherColumns: Select your first 3 columns and choose - UnpivotOthers, so you're flexible when new attributes come in.

let
    Quelle = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    UnpivotOthers = Table.UnpivotOtherColumns(Quelle, {"type", "description", "ID"}, "Attribut", "Wert")
in
    UnpivotOthers

 

[harperjo]

I have not used power query as of yet.....just the cursory walk through. I was looking at that also to to produce a record set for each of the attribute columns and then merge the lot at the end...........

I am not certain how to pull all of this together but I will give it a look............thanks.

If you can give a little more explanation of you solution that would be very helpful.

Thanks again for the reply.....appreciated.

 

[ImkeF]

Let's give it a try

Select your table, format as table (if it isn't already) and name it "Data".
Go to: Power Query - Get external data - From other sources - down below: Blank Query.
Replace everything you see by my code from above.