I have data which is in the following format, and I have been searching for hours now how to transform it into the correct format, and I can't seem to find the correct answer.
I have the following table
AA | NULL | NULL | NULL | NULL | BB | NULL | NULL | NULL | NULL | |
2019 | 2020 | 2021 | 2022 | NULL | 2019 | 2020 | 2021 | 2022 | NULL | |
1 | 8 | 901 | 3 | 8 | NULL | 6 | 5 | 2 | 0 | NULL |
2 | 500 | 509 | 4 | 6 | NULL | 1 | 8 | 5 | 2 | NULL |
3 | 205 | 5 | 8 | 9 | NULL | 6 | 8 | 4 | 6 | NULL |
And I simply need to convert it to
Number | Year | Name | Value |
1 | 2019 | AA | 8 |
2 | 2019 | AA | 500 |
3 | 2019 | AA | 205 |
1 | 2020 | AA | 901 |
2 | 2020 | AA | 509 |
... | ... | ... | ... |
And so on. I tried all sorts of transposing and crosstabs and even the arrange tool, but I cant seem to find a correct solution. Please help.
Solved! Go to Solution.
Hi @DADE, take a look at the approach in the attached solution. It replicates the output you are looking for. Run it across your larger dataset and let us know if you run into any challenges.
works like a charm! Thanks
Hi, @DADE
This should take care of it for you.
Please mark as an acceptable solution + like, if it works for you.
The workflow is attached.
Cheers!