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 fairly 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 adding 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:

let
    Source = CommonDataService.Database("taco.crm.dynamics.com"),
    superQuery = Value.NativeQuery(Source, "Select * from nwind_orders")
in
    superQuery

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.

let
    Source = CommonDataService.Database("taco.crm.dynamics.com"),
    superQuery = Value.NativeQuery(Source, "Select * From nwind_orders Where nwind_orderstatusidname = 'New'")
in
    superQuery

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!

Power BI and SharePoint Person or Group Field

I created a simple Power BI report to pull data from a SharePoint list and quickly ran into a problem with the Person or Group column type.

Error:
Expression.Error: We cannot convert the value “” to type Table.
Details:
Value=
Type=[Type]

In the report, I was trying to expand a Person or Group column, but not all of the field values were populated.

Here is the fix:

let
    Source = SharePoint.Tables("https://taco.sharepoint.com/sites/food", [Implementation="2.0", ViewMode="All"]),
    #"abcc-b8fe-4b23-be01-abc5f2c3320c" = Source{[Id="abcc-b8fe-4b23-be01-abc5f2c3320c"]}[Items],
    #"Expanded Assigned User2" = Table.TransformColumns(#"abcc-b8fe-4b23-be01-abc5f2c3320c", {{"Assigned User", each if Value.Is(_,type list) then _{0} else [title = "not assigned"], type record}} ),
    #"Expanded Assigned Analyst" = Table.ExpandRecordColumn(#"Expanded Assigned User2", "Assigned User", {"title"}, {"Assigned User.title"})
in
    #"Expanded Assigned User"