Folding multiple columns by MULTIPLE patterns

Hello, I have a large number of columns that all follow the same pattern, with 3 parts that can change: Path#_Field_Year
So either the Path #, Field, or Year could vary. For example, I could have:
1_Car_2030 1_Car_2031 1_Car_2032 2_Car_2030 2_Car_2031 2_Car_2032
Or I could have:
2_Bus_2040 2_Bus_2041 2_Bus_2042 5_Bus_2030 5_Bus_2031 5_Bus_2032
I want to transpose or "fold" the columns into rows, but I still want to have columns for the Path #, Field, and Year so I can identify the different combinations. The VALUE column would just equal the original number associated with the Path#_Field_Year combination as below:
Path # | Field | Year | VALUE |
---|---|---|---|
1 | Car | 2030 | 435 |
1 | Car | 2031 | 44 |
1 | Car | 2032 | 53 |
2 | Car | 2030 | 742 |
2 | Car | 2031 | 7 |
2 | Bus | 2032 | 32 |
etc | etc | etc | etc |
I found the article below that would help me if I only had ONE pattern, but I have multiple patterns to deal with.
Thank you in advance - I'm a relatively new user and very much appreciate the power of Dataiku when it comes to wrangling data.
Operating system used: Windows
Operating system used: Windows
Answers
-
Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 2,248 Neuron
Do it 2 times?