Select and Filter OData feeds in Power BI
OData (Open Data Protocol) is used for interacting with RESTful APIs. OData feeds are typically easy to consume due to the metadata these feeds use. The metadata acts as a description for the feed which renders the data in a consumable fashion, and in Power BI it aids with setting the correct type for the columns.
More information on the OData protocol can be found in the Microsoft Documentation.
But what happens when someone breaks the metadata? or you simply want to only retrieve Select columns or Filter for entities within the feed. Well within Power BI it is possible to use a Query within the header to either Select, Filter, or even use Select & Filter.
In this post we will look at how to achieve this, and what the Pro’s and Con’s of performing these actions have.
Pro’s
Con’s
This post will be focused on using the Graph API OData feed. For those who have seen one of our speaker session at MMS or TunedIn London, or have seen the previous blog post (Graph API & Power BI – MMS Edition) will know that this is something we use on a daily basis.
This post originated from an issue that occurred around the 13th January 2024. Where the deviceManagement/managedDevices/ endpoint became ‘bust’ due to a complex type in the metadata been removed.
Using a Select Query for OData
Select queries are extremely useful when it comes to gathering just the data you need to complete your task. Think of it as removing those non-essential items from your shopping bag, if you don’t need them, why have them.
If we look at the Microsoft Documentation on $select for Graph API, you can see examples in various language formats and you can also see the Microsoft Recommendations.
Whilst it is possible to append URL with ?$select=, when the OData metadata is malformed you will still receive an error, even if you are not selecting that property.
For exmaple if we apend the GraphBase with ?&select=id,deviceName we would get the following error.
Taking the above warning into account, lets take a look at a more efficient way to use the select query for OData feeds in Power BI, which will allow you to workaround the malformed metadata.
Lets use the below query code as an example (this is taken from the resources in my previous POST;
On lines 13-16 lies our Query header. These are controlled by variables on lines 3 & 4. Let’s say I only wanted to select the id property for each device. I would simply add id to the GraphSelect. However, if I wanted to select the id & deviceName we would add id,deviceName. To continue adding properties you simply just need to add a comma between the values. See the below example;
Using the above GraphSelect would return just the above values as shown below;
As you can see this has worked around the issue with the metadata as mentioned in the warning.
Using the select capabilities is not just a workaround tool, I would recommend using this in all of your queries to select only the fields that you need, it is more efficient, precise and allows you to remain in control of your report data landscape.
Using a Filter Query for OData
Filter queries are extremely valuable when you are looking to retrieve data matching a set criteria.
Further information on the filter query can be found in the Microsoft Documentation.
Similar to the Warning in the Select Query section, applying the filter as an appendage to the URL will result in the same error, the use of the filter options should be used to compliment the select options.
We are going to use the same Template Query as we did in the select section, but we are going to pivot to using the packaged applications API endpoint as shown below.
Using the above GraphSelect would return just the above values as shown below;
Now lets imagine you only wanted to see Windows applications, OData doesn’t return the OData.type so it is incredibly difficult to filter this within Power BI, and you would also suffer from poor performance to load and then filter the data.
So we would change the GraphFilter to the following;
Let’s not get distracted by all of different filter parameters, instead lets look at what this provides as an output.
If you want to see all apps including iOS, Android and Windows, but filter out apps that appear that are used for App Protection policies etc, you can use the following filter;
Conclusion
As you can see in this post, the select & filter options for OData feeds within Power BI are extremely useful. I would highly recommend using these to improve the performance of your reports, and I hope this helps you take control of your data.
Categories
Tags
Select and Filter OData feeds in Power BI
OData (Open Data Protocol) is used for interacting with RESTful APIs. OData feeds are typically easy to consume due to the metadata these feeds use. The metadata acts as a description for the feed which renders the data in a consumable fashion, and in Power BI it aids with setting the correct type for the columns.
More information on the OData protocol can be found in the Microsoft Documentation.
But what happens when someone breaks the metadata? or you simply want to only retrieve Select columns or Filter for entities within the feed. Well within Power BI it is possible to use a Query within the header to either Select, Filter, or even use Select & Filter.
In this post we will look at how to achieve this, and what the Pro’s and Con’s of performing these actions have.
Pro’s
Con’s
This post will be focused on using the Graph API OData feed. For those who have seen one of our speaker session at MMS or TunedIn London, or have seen the previous blog post (Graph API & Power BI – MMS Edition) will know that this is something we use on a daily basis.
This post originated from an issue that occurred around the 13th January 2024. Where the deviceManagement/managedDevices/ endpoint became ‘bust’ due to a complex type in the metadata been removed.
Using a Select Query for OData
Select queries are extremely useful when it comes to gathering just the data you need to complete your task. Think of it as removing those non-essential items from your shopping bag, if you don’t need them, why have them.
If we look at the Microsoft Documentation on $select for Graph API, you can see examples in various language formats and you can also see the Microsoft Recommendations.
Whilst it is possible to append URL with ?$select=, when the OData metadata is malformed you will still receive an error, even if you are not selecting that property.
For exmaple if we apend the GraphBase with ?&select=id,deviceName we would get the following error.
Taking the above warning into account, lets take a look at a more efficient way to use the select query for OData feeds in Power BI, which will allow you to workaround the malformed metadata.
Lets use the below query code as an example (this is taken from the resources in my previous POST;
On lines 13-16 lies our Query header. These are controlled by variables on lines 3 & 4. Let’s say I only wanted to select the id property for each device. I would simply add id to the GraphSelect. However, if I wanted to select the id & deviceName we would add id,deviceName. To continue adding properties you simply just need to add a comma between the values. See the below example;
Using the above GraphSelect would return just the above values as shown below;
As you can see this has worked around the issue with the metadata as mentioned in the warning.
Using the select capabilities is not just a workaround tool, I would recommend using this in all of your queries to select only the fields that you need, it is more efficient, precise and allows you to remain in control of your report data landscape.
Using a Filter Query for OData
Filter queries are extremely valuable when you are looking to retrieve data matching a set criteria.
Further information on the filter query can be found in the Microsoft Documentation.
Similar to the Warning in the Select Query section, applying the filter as an appendage to the URL will result in the same error, the use of the filter options should be used to compliment the select options.
We are going to use the same Template Query as we did in the select section, but we are going to pivot to using the packaged applications API endpoint as shown below.
Using the above GraphSelect would return just the above values as shown below;
Now lets imagine you only wanted to see Windows applications, OData doesn’t return the OData.type so it is incredibly difficult to filter this within Power BI, and you would also suffer from poor performance to load and then filter the data.
So we would change the GraphFilter to the following;
Let’s not get distracted by all of different filter parameters, instead lets look at what this provides as an output.
If you want to see all apps including iOS, Android and Windows, but filter out apps that appear that are used for App Protection policies etc, you can use the following filter;
Conclusion
As you can see in this post, the select & filter options for OData feeds within Power BI are extremely useful. I would highly recommend using these to improve the performance of your reports, and I hope this helps you take control of your data.
