Forum Discussion
Helper ISwitching from CDS to Dataverse - Unpivot Columns no longer supported & row limit
Try this. On the step before the unpivot, in the formula bar, wrap the entire thing with Table.Buffer(…original formula…)
if the data is too big this won't work but it will prevent sending the unpivot operation to the server. Sometimes SQL cannot handle an unpivot. Dataverse is based on SQL.
AdministratorI've just tried it again, the only steps I have followed in Power Query are remove columns and then unpivot columns and I get the following error when using the Dataverse connection.
Community ChampionTry this. On the step before the unpivot, in the formula bar, wrap the entire thing with Table.Buffer(…original formula…)
if the data is too big this won't work but it will prevent sending the unpivot operation to the server. Sometimes SQL cannot handle an unpivot. Dataverse is based on SQL.
xuxxay88You Sir, are a hero!
That worked perfectly,
For anyone who can't find the formula bar, go to file > options and settings > options > power query editor > at the top part of the screen check "Display the Formula Bar"- edhans
Yes! You always want that formula bar showing. Glad it worked. Sometimes when using Table.Unpivot* it tries to fold that back to the SQL server, and it occassionally doesn't work. So Table.Buffer ensures the Power Query engine does it itself.