One of our users had a question which is worth publishing on the Community. While it might be difficult to understand the case in full details without seeing the source data, the question and the answer here can hint at a solution in similar cases.
Published with permission and minor edits.
I am having an issue with the pivot transformation. I am pivoting a table with 10,872 records. I am expecting the pivot to create 3 columns with the associated data (labels and data).
Instead, it is only pivoting once so the final row count is missing. I am getting 453 rows instead of 3-4 times that amount.
Here are the values from “Items”
Here are the items from the data field:
When Pivot is applied using “Any” for data it looks to work.
However, not all of the rows are pivoting back and most are being dropped (2 out of every 3 rows).
This has never happened before (I used pivot all of the time). I am using the latest version of easymorph 184.108.40.206.
There is no problem with the Pivot transformation – it operates as expected. Here the project is using the aggregation function “Any” that leaves only 1 value from multiple values inside each group formed by combination of values in the rest of columns besides “Item” and “Data”. You can only use “Any” for pivoting back, when you’re sure that there is only 1 value per group per new column, so no data is lost. That’s why we need to enumerate rows before unpivoting (as it was suggested in this thread: Pivot back to original dataset) . By adding a row number we ensure that each line is a separate group.
However in this case you are pivoting back into different groups than the original ones because the new groups also include Goal. Since there are only 453 old groups, so Pivot correctly rolls them up to a single value per group using the Any function and throwing away other values in groups.
The fix is to enumerate the new groups correctly. Since it now includes goals, the goal number can be appended to RowNo, thus producing a new group ID. Now Pivot doesn’t throw away any data because there are exactly 3 values per each group that are pivoted into 3 columns - every value goes into one of the new columns.