Forum Discussion
Unstructured data transform question
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
Hi davidmeblack
See the attached file here. Hope it will help... See the Merged Table in Query Editor
Hi,
You may refer to my Blog article - Restructure the layout of datasets. Refer to Case 1.
Hope this heps.
4 Replies
Hi davidmeblack
See the attached file here. Hope it will help... See the Merged Table in Query Editor
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
Hi,
You may refer to my Blog article - Restructure the layout of datasets. Refer to Case 1.
Hope this heps.
Perfect thanks so much guys, really appreciate it!
