Unstructured data transform question

[davidmeblack]

Hi guys,

A piece of software I use exports its .csv files in a very... bad way. See below

Name	DescriptionA	ValueA	DescriptionB	ValueB	DescriptionC	ValueC
Car1	Door	2	Window	4	Trunk	6
Car2	Door	5	Window	8	Trunk	1

I want to transform it from that to this:

Name	Description	Value
Car1	Door	2
Car1	Window	4
Car1	Trunk	6
Car2	Door	5
Car2	Window	8
Car2	Trunk	1

So basically grouping all descriptions and values. I've tried every combination of pivot/unpivoting/transpose but I can't get the correct result! There's currently ~180 columns of that description/value format it spits out so if anyone has any tips it would be greatly appreciated!

Thanks,
Dave

[Zubair_Muhammad · Accepted solution]

Hi @davidmeblack

See the attached file here. Hope it will help... See the Merged Table in Query Editor

[davidmeblack]

Perfect thanks so much guys, really appreciate it!

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my Blog article - Restructure the layout of datasets.  Refer to Case 1.

Hope this heps.

[Zubair_Muhammad · Accepted solution]

Hi @davidmeblack

See the attached file here. Hope it will help... See the Merged Table in Query Editor

[Zubair_Muhammad]

@davidmeblack

Here are the steps

1) Unpivot all columns other than Car

2) Filter Rows that CONTAIN word "Description"

3) Add an Index Column

Now Create a Duplicate Query

1) Filter rows that CONTAIN word "Value"....

Now we can merge these 2 Queries