Direct Query not working with Dataverse and PBI dataset in Power BI Service

[JaideepS]

I have direct query data sources from dataverse and Power BI dataset and the report works fine in the PBI Desktop but gives error in the portal when the report is published. 

[JaideepS]

Infact, i am using a composite model (from dataverse and another the Power BI dataset) and the report works fine in the PBI Desktop but gives error in the portal when the report is published. 

[v-cazheng-msft]

Hi @JaideepS ,

May I know what kind of error did you get? Did you get error when you try to view the report in Power BI service? Have you ever checked the Settings page of the report dataset to see whether you need configure gateway or edit credentials for it?

Best Regards,

Community Support Team _ Caiyun

[JaideepS]

@v-cazheng-msft Find the error:

I have edited the credentials and no gateway needed:

Hope it can be fixed quickly.

[JaideepS]

@v-cazheng-msft Is there anything else I can help with to speed up the investigation? This is blocking us from a tool release, would appreciate a quick fix.

[v-cazheng-msft]

Hi @JaideepS ,

Sorry for the delay. I found an similar error, this issue may be caused by the handshake process couldn’t be completed within 15 seconds between gateway and sql data source. If for some reason it cannot complete or it takes more than 15 seconds. Then you may get the error message as the follows:

There was an error when processing the data in the dataset.

May I know whether your SQL Server data source and your gateway are installed on different machines? Is your gateway the latest version? Could you please try to install the latest version of gateway on the data source machine and let me know the result? If it doesn’t work, I could provide other solutions for you. Thanks in advance!

Best Regards,

Community Support Team _ Caiyun

[JaideepS]

@v-cazheng-msft It seems there was a transformation step added into the direct query and that was causing this issue. Now it works when this step is removed.

Is there any plan to add the possibility to have transformations on top of the direct query? Maybe it is illogical still not impossible?!

[v-cazheng-msft]

Hi @JaideepS,

There are some transformation limitations on  Direct Query connection mode itself and connection to Power BI datasets with Direct Query currently, which you could have a look at the following docs.

Using DirectQuery in Power BI - Power BI | Microsoft Docs

Using DirectQuery for datasets and Azure Analysis Services (preview) - Power BI | Microsoft Docs

For the release of some limitations, you could submit your needs in Power BI Ideas or vote for some fabulous ideas there. Thanks in advance!

https://ideas.powerbi.com/ideas/search-ideas/?q=transform

Also, you could subscribe official blog to receive the updates in Power BI at first time.

Power BI Blog—Updates and News | Microsoft Power BI

Best Regards,

Community Support Team _ Caiyun