Pivot app query
Hi - I created a Pivot and then did a lot of customization on the Pivot so I don't want to lose it.
I updated it several times by tweaking the content in the source data and it still imported/refreshed ok.
Then tonight I renamed 2 column headers and then it broke.
I renamed them back to the original names and it still won't work.
I get error message;
Error! The columnId 7106294493538180 is included more than once in a single row. (1037)
Is this expected behavior?
When I created the pivot I created it by adding 1 value at a time as I was getting some errors, this got me around that challenge.
With the way the column mapping works - can I 'rebuild' the pivot using the existing source file to the existing destination file and it will use the same column mapping again? Or is there a risk that by re-adding values again it will cause new columns to be entered and undo all my prior work?
Hope that makes sense? Thanks!
Best Answer
-
Hi Genevieve - I was also advised to turn off any extensions when running pivots - this was actually super helpful advice as they kept timing out when creating, since then my pivots seem stable and I haven't been having the issues I was previously.
Answers
-
Hi @.Leon.
I've seen this error "The columnId 7106294493538180 is included more than once in a single row." when a user has mapped a column that is also listed as the "Look up value" under the "Data field", but that is for the DataMesh application.
This means that for Pivot, it may be that the columns that the Pivot is attempting to create are too similar, due to the input values from the source sheet. You could try defining less rows in the initial setup, then add additional rows and values in subsequent edits. It sounds like you may have done this already with the initial creation.
If this hasn't helped, I would recommend working directly with Smartsheet Support so that you can share screen captures and screen recordings of your current set-up and issue.
Thanks,
Genevieve
-
Thanks - I did do that incremental approach based off an old forum post you made, got it working, then slowly built out formatting & additional column structures & formulas. Then after inputting new source data, with exactly the same column structure & headers, and running it - it broke.
This has happened 5+ times now making me think Pivots are generally an unstable product.
My alternative path is - create 3-4 reference pivot tables with less columns included.
Then create a summary pivot that dynamically creates the same index structure off the least amount of inputs (so it is less prone to breaking), then using INDEX MATCH to pull in data from the reference pivot tables.
That way if I re-run the reference pivot tables and they break, I can easily recreate them and update the references as no additional work would have been done on them regarding formulas/formatting etc.
And hopefully the summary pivot having super minimal data involved to create it will be less prone to breaking long-term.
Thoughts on this approach?
-
Hi @.Leon.
Breaking it out into smaller pieces potentially sounds like a good idea, especially if you have enough data to split into 4 separate Pivots. It's hard to know if this is the best approach without visualizing the full extent of your process.
Do you have access to Pro Desk? If you do, I would suggest booking a 30-minute session under Premium Apps to go through your process over a screen share and see if there's a better way to set this up.
Cheers,
Genevieve
-
Hi Genevieve - I was also advised to turn off any extensions when running pivots - this was actually super helpful advice as they kept timing out when creating, since then my pivots seem stable and I haven't been having the issues I was previously.
-
Hi @.Leon.
This is great to know! Thank you for sharing the detail about extensions.
