Using Power BI to View Statuses in Dynamics 365

In Dynamics 365, we can use Power BI to see statuses of records. Let’s go through an example of how to do this with Opportunities. Opportunity statuses include:

State Code: 0 = Open, 1 = Won, 2 = Lost
Status Code: 1 = In Progress, 2 = On Hold, 3 = Won, 4 = Canceled, 5 = Out-Sold

The Opportunity Close entity also has statuses:

State Code: 0 = Open, 1 = Completed, 2 = Canceled
Status Code: 1 = Open, 2 = Completed, 3 = Canceled

Let’s create a new Opportunity:

Let’s look at the state and status of this opportunity in Power BI. Open Power BI and enter the following blank query,

[sourcecode language=”CSharp”] let
Source = OData.Feed("https://yourorg.api.crm.dynamics.com/api/data/v9.1", null, [Implementation="2.0"]),
opportunities_table = Source{[Name="opportunities",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(opportunities_table,{"statecode", "name", "statuscode", "opportunityid"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([name] = "My new opportunity"))
in
#"Filtered Rows"
[/sourcecode]

We see the state code is 0 (Open) and status code is 1 (In Progress):

Let’s also grab the OpportunityCloses entity with the query:

[sourcecode language=”CSharp”] let
Source = OData.Feed("https://yourorg.api.crm.dynamics.com/api/data/v9.1", null, [Implementation="2.0"]),
opportunitycloses_table = Source{[Name="opportunitycloses",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(opportunitycloses_table,{"statecode", "statuscode", "_opportunityid_value", "opportunitystatecode", "opportunitystatuscode"})
in
#"Removed Other Columns"
[/sourcecode]

At this point, the Opportunity Id does not exist in the Opportunity Closes table.

Let’s change the status to “On Hold”:

Save the opportunity and refresh. The opportunity status code is now 2 (On Hold):

Now change it to Open for Bidding:

Change to Delivered:

At this point, it is not in the Opportunity Close table.

Let’s change back to In Progress, then close the opportunity. The 2 options are:

Purchased
Won

Let’s set to Won and click OK. Refresh Power BI to see the updated statuses. The State Code is 1 (Won) and the Status Code is 3 (Won):

At this point, the opportunity is in the Opportunity Close table.

Reopen the opportunity by clicking Reopen Opportunity from the toolbar:

Close the opportunity as Won. The state is now 1 (Won) and the status 100000004:

Now reopen and close as Lost. We see 2 status reasons – Canceled and Out-Sold:

2 = Lost, 4 = Canceled:

Each of the Opportunity Close records has a different timestamp, the latest one at the bottom shows State Code = 1 (Completed), Status Code = 2 (Completed):