Dynamics 365 to Power BI via Direct Query

[swebb]

All,

I'm struggling with a recent task. I'm able to query the Opportunities table, however, I'm running in to one issue after another. Part of it might be the data model/requirements, but there are some pretty "simple" things that I think one would be able to accomplish that I am struggling with. 

1. I'm loading data from Dynamics 365 opportunity table to Power BI Service in order to provide a dashboard to my sales team. 

2. One issue I'm experiencing is the date columns that I've switched to "Date Only" are showing long date "Monday, January 9th, 2023" vs "01/09/23" in the visual table. What gives? 

3. I had some issues with what I thought only the UID showing up, but looks like I've figured that out. Seems like each field has its own UID and corresponding text field that it is paired with, correct?

4. Jumping off from the UID issue, I'm seeing that there are numerical values where there should be choices. I assume this is because in Dataverse choices are generally associated with random number UIDs? I don't see the pairing/choices in the table that I've imported in to PBI. Do I have to have another table with relationships to "map" those numerical IDs with the choices in PBI?

[pavanmanideep]

Hi,

I hear your concerns...in order to get the data in the visual the way you want, you need to perform ETL Transformations in Power Query.

For getting the optionset labels instead of values, you can pull out the string map table from Dataverse and join with actual contact or account table or wherever you source data is.

Hope this helps...transformations in Power Query is must for any Power BI Report for desired look and feel..

If I have answered your question, please mark your post as Solved.

If you like my response or got a direction forward to proceed, please give it a Thumbs Up. Appreciate your Kudos. You can accept more than one post as a solution.

Cheers,

PMDY

[JpBio23]

Hello budy, how are you? Were you able to connect Dynamics to Power BI via Direct Query? I tried to do it at PBI, I researched videos and blogs and I couldn't find a way to do it.

If you did, can you show me how?

[pavanmanideep]

Hi,

In Power BI, you can look for get data Dataverse or SQL Server and choose your desired instance.

I have here selected SQL Server and provided my Dataverse instance details, once authenticated, I can connect to the tables using Direct Query or Import mode as below

Hope this helps...

If you like my response or got a direction forward to proceed, please give it a Thumbs Up. Appreciate your Kudos.

Cheers,

PMDY