Expression Error : Transformation Logic is not supported as it requires dynamic access to rows of data, which cannot be scaled out in adf power query

Question

Hi,
I have created a power query which is connected to two datasets of csv type as shown

To perform some transformations on these datasets i created two queries referring to both the datasets as

and

In both the query i want to promote first row as headers but i am getting an error

Same is happening with the other query

Also when i am using transform to json i am getting this error -

Is there any way to achieve this in adf power query or this is not supported in adf power query ?

Answer 1

Hello @Amar Agnihotri ,
Thanks for the question and using MS Q&A platform.

Uhh Hi, I see you ran into something similar. I know its confusing, as you can see something working in authoring, but then getting a message saying it won't work.
For solution on promote headers, go to bottom.

Link to explanation wrangling-functions

While in this development / authoring view, everything is done together, it doesn't use the same compute as a real run would. The real run scales out, and to do that breaks the data into chunks to be handled by different workers, like copy activity can.
When a transform in one chunk requires data from another chunk, in a way that cannot be planned for ahead of time, a problem arises. We want each worker to operate independent from other workers. That is why some transforms are not supported.

Promote Headers is one of the known unsupported functions.
Transform columns should work.
I'm not sure why the json transform isn't available. I'm hoping they just haven't gotten around to implementing it yet.

However, I can tell you how to arrange so you do not need to use "Promote Headers"!
First of all, create the dataset outside of the power query. The "New dataset" from inside power query seems broken to me. In that dataset, set first row as header option, and most important import the schema in the dataset.





Please do let me if you have any queries.

Thanks
Martin

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators