Switching from CDS to Dataverse - Unpivot Columns no longer supported & row limit

[xuxxay88]

Hi,

I was in the process of switching the connection to some tables from the Common Data Service over to Dataverse as there were concerns of "what if Microsoft turn off CDS?" so trying to be pro-active I started attempting to switch them over.

One of the steps in my transformation of one of the tables involved unpivoting columns and this worked perfectly with CDS but appears to give a SQL error with dataverse and I can't seem to find a way around it.

Should I be concearned that CDS may stop being supported?
Is there a fix for this unpivot issue? I have seen advice related to SQL however none of the tables connected via the CDS required any SQL from myself.

Another issue I ran into was for some reason it was refusing to import the table due to hitting a row limit, however, I do not believe to be quite that much data in the table I was connecting to, there is a lot, but not THAT much.

Any help is much appreicated, thanks.

[Syndicate_Admin]

There is no reason unpivot shouldn't work in the Dataverse connector. In fact, CDS and Dataverse are the same. Dataverse just has new names (Tables and Columns for Dataverse vs Entities and fields for CDS) but the tech is hte same. In fact, in Power BI, when you connect to Dataverse, it still shows CDS as the connection:

And still uses the = CommonDataService.Database("yourdomainhere.dynamics.com") function.

[edhans]

There is no reason unpivot shouldn't work in the Dataverse connector. In fact, CDS and Dataverse are the same. Dataverse just has new names (Tables and Columns for Dataverse vs Entities and fields for CDS) but the tech is hte same. In fact, in Power BI, when you connect to Dataverse, it still shows CDS as the connection:

And still uses the = CommonDataService.Database("yourdomainhere.dynamics.com") function.

[Syndicate_Admin]

I'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.

[edhans · Accepted solution]

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. 

[xuxxay88]

You 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.

[xuxxay88]

I'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.