Get the display value of a dataverse choice field in Power BI

I’m working on a simple report to pull some data from Dataverse into a Power BI report. The data includes some choice fields, and when I first generated the report, I only retrieved the internal value of the fields. So my Status field values look something like this: 10000001 Cleary, no one wants to see this, and I needed to grab the display values: Ordered, Processing, Shipped

Part of the problem was related to using Native Query to pull in the data and not selecting the correct field. Meaning there are two or more columns for each choice field.
My query looked something like this:
Select title, status, createdon from orders where customerid = ‘875-6309’

The query should have been:
Select title, statusname, createdon from orders where customerid = ‘875-6309’

try placing the word ‘name’ directly after your choice field name.
status would be statusname
state would be statename

Filter a Power BI Source Before Importing

This has bugged me for a long time, and I wasn’t sure how to get around it. I was tasked with creating a Power BI report using data from a reasonably big data set, but the report only displayed a really small part of it. The source is in the neighborhood of 10,000,000 rows, and my report displayed roughly 100,000. Why try to direct query, import, or incrementally refresh all of the data when such a small part of the data is needed? I was looking for a way to filter the data set before you add the applied steps in the query editor. Something like: Select * from Orders where Status = Open

The answer I was looking for is Native Query, and I’m not sure how I completely failed to learn about this until now.

For this example, I’m using the Northwind Traders orders dataverse table. Connect to the dataverse environment, select the orders table, then open the Advanced Editor window. Change the query to this:

    Source = CommonDataService.Database(""),
    superQuery = Value.NativeQuery(Source, "Select * from nwind_orders")

All I’m doing here is checking to see if the newly formed query is working. Next, right-click on the step where you created the Native Query in the Applied Steps window and select view Native Query. Update the query only to show New orders. This can be done by adding a Where clause:

Select * From nwind_orders Where nwind_orderstatusidname = 'New'

Click Ok, and the query should refresh, only showing new orders.

    Source = CommonDataService.Database(""),
    superQuery = Value.NativeQuery(Source, "Select * From nwind_orders Where nwind_orderstatusidname = 'New'")

That’s all it takes to filter the data set before transforming it! Be sure to check the linked documentation and take a look at query folding.

Power BI Web URL Link

What happened to the Web URL link in Power BI? With the latest update to Power BI Desktop, Microsoft made some UI changes. If you are looking for the Web URL option, make sure your data grid / table is selected, click the Format visual button, expand Cell elements, at the bottom you’ll find Web URL. Switch it to On and you’ll be ready to go!