Replace empty cell as text with real empty value

12

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

image
image.png1058×298 13.9 KB

  1. Import the file
  2. Unpivot all columns except Column 1
  3. Remove empty rows from column Data
  4. If column ‘Labels’ ends with ‘1’, rename to ‘Label’, else leave empty
  5. Pivot based on column Data both as column labels and data (type Any)
  6. Sort column 1 in ascending order
  7. Remove column Labels