Forum Discussion
Gen 2 dataflow fails validation with "Pivot column" query
Hi all,
Has anyone else encountered this? I am at a complete loss...
I have had a Gen 1 dataflow running for over a year without any problem, but from the first moment I saved it as Gen 2, it stopped working. I keep getting this validation error on some of my queries:
"Schema evaluation did not return TableResult. Actual result type: Cancellation, Evaluation cancelled, Message: Evaluation is canceled"
After some further digging, it appears that this error message appears for all the queries that have a "Pivot column" step, and only for those queries... Which is weird, again, because I never had an issue with them in the Gen 1 dataflow and in the Power Query editor the step does operate and I can successfully see the action happening. But then I save the dataflow and the validation fails with the message above.
Does anyone know what is happening here? And maybe how to fix this?
With Microsoft "killing" Gen 1 dataflows I need to be able to convert it to Gen 2 sooner rather than later.
Thanks in advance for anyone's help.
6 Replies
- ibarrau
Super User
Hi. The Power Query editor preview and the Dataflow Gen2 validation engine are not identical. It's possible for a step to preview successfully but fail during the background schema evaluation that occurs on save. You can try:
- Recreate one failing Pivot step from scratch in a new query to rule out corruption.
- Recreate one of the tables with pivot in a different dataflow step by step.
If those two won't work, then the issue is not something broken at the Gen2 item but a validation at the code. Try:
- If possible, materialize the data before the Pivot (for example, split the logic into two tables for stage the table first) and then perform the Pivot downstream.
- If you're using an aggregation in the Pivot, verify there aren't duplicate key combinations that could be confusing the evaluation.
I'm not sure why the error happen, but those are some ideas I would have tried.
I hope that works for you.
Hi ibarrau,
I alreayd tried all the options you listed and nothing worked... 😅
I spent the last 3 weeks investigating this issue and testing different ways to go around it.
Finally today I decided to try saving the dataflow after each single query step and let the validation run each time. And that's when I noticed that the validation would fail systematically after the "Pivot column" step. I tried it out 4 times with different data tables and still got the same exact error at exactly the same moment. Which lead to my conclusion that there is some kind of issue with the "Pivot column" query specifically.
- GilbertQ
Hi aury_lola
What I would recommend doing is taking your error that you have as well as your Power Query code and putting it into your favorite LLM and asking them to refactor this to see if that will resolve your issue. It might save you a lot of time and effort and then you can review the code to see what has changed to make it work successfully. Also could you make sure if you try a Data Flow Gen 2 with CICD as this is the latest version.
Hi GilbertQ,
I'm not sure I understand what you mean by refactoring my query through LLM...
Anyway, yes it is a Gen2 CI/CD dataflow.
I don't know if you have read my reply to ibarrau below, but I tried multiple things for the past 3 weeks. And even if the "Pivot column" is literally the first step in my query, it fails the validation always with the same error message.
v-saisrao-msftCommunity Support
Hi aury_lola,
Thank you ibarrau, GilbertQ for your insights.
Based on your troubleshooting, you've already isolated the issue to the Pivot Column step rather than the source data or overall query. Microsoft documents that Dataflow Gen2 performs schema validation during publish, but there isn't any documented limitation or known issue specific to Pivot Column causing this validation error. Given that you've reproduced it across multiple datasets, the next step would be to create a minimal reproducible example and open a Microsoft Fabric support ticket for further investigation.
Dataflow Gen2 data destinations and managed settings - Microsoft Fabric | Microsoft Learn
Create a Fabric and Power BI Support Ticket - Power BI | Microsoft Learn
Thank you.
I was hoping it would not come to that.
I'll notify my company administrator to see if they have also experienced this issue and in case have them create the ticket.
Thank you!
