problems with directquery to dynamics cds

[fuglphoenix]

i've been experimenting with the CDS and direct query, using this guide https://crmchartguy.com/2020/05/29/directquery-cds-from-power-bi/ and it has been working great for a few weeks/months.

but suddenly it doesn't work anymore, i haven't touched my setup and it started to give me an error in the pbi visuals:

Index was outside the bounds of the array.. The exception was raised by the IDataReader interface

i'm not manipulating my data at all, so why i get an index out-of-bounds i can't figure out.

it works just fine when i view the data in the "Transform data" window. it is only in the report pages the error appears.

my powerquery:

let
    Source = Sql.Databases("mycompany.crm4.dynamics.com,5558"),
    orgXXXX= Source{[Name="orgXXXX"]}[Data],
    dbo_systemuser = orgXXXX{[Schema="dbo",Item="systemuser"]}[Data]
in
    dbo_systemuser

I know that the functing is experimental, and thus it might be unstable sometimes. but is there something I can do to make it work, or should i just wait...

edit:

i tried to change to "import"-mode instead of directquery. and i works just fine 🤔

[v-xuding-msft]

Hi @fuglphoenix ,

I found a video that use the Power Apps visual in Power BI to add/update data in CDS using Direct Query Mode.  You could have a try.

https://community.powerbi.com/t5/Power-Platform-Integrations/Power-Apps-Visual-for-Power-BI-CDS-Dire...

And I see the comment from https://crmchartguy.com/2020/05/29/directquery-cds-from-power-bi/. There is other user that encounters this issue and he/she changes to import mode as well. Then the report works fine. Maybe it happens because it is preview?  I haven't tried it. If you need to use this way to do. You could create a support ticket to get the dedicated support.

How to create a support ticket in Power BI  

[lbendlin]

your Power query is incomplete. It just connects to the table "systemuser"  (in a roundabout way).  What happens next?

[fuglphoenix · Accepted solution]

@lbendlin, i'm not sure I understand what you mean.

because it does load the data from  db tabel systemuser into dbo_systemuser, which i load directly into the pbi datamodel.

it looks fine in the powerquery window (ie. i see the data), but as soon as I apply and load it into pbi it breaks.

unless I switch from directquery to import. that will fix the issue...

okay. this i strange. I was about to get some screenshots of the issue. 

and despite being broken for a few weeks, and i haven't change anything. it suddendly works...

i guess that i might have been a problem with cds endpoint

[v-xuding-msft]

Hi @fuglphoenix ,

Today, I find that this is listed as a known issue internally.

There are two scenario about it. One is with SSO enabled.  This is still fixing.

Another is without SSO. It is fixed. If you meet this scenario, please try again to check if it is working on your site.