Hi @Dinho
I think I solved it for you - This is the best way I could find, without having to know the exact empty cells. The other option I played with was the ‘Shift column up/down’ trransformation, but this required me to know which cells were empty.
TheDinhoProblem.zip (7.4 KB)
Input/output as follows
- Import the file
- Unpivot all columns except Column 1
- Remove empty rows from column Data
- If column ‘Labels’ ends with ‘1’, rename to ‘Label’, else leave empty
- Pivot based on column Data both as column labels and data (type Any)
- Sort column 1 in ascending order
- Remove column Labels