piątek, 8 grudnia 2017

How to create dynamic OData query in Power BI?

Power BI allows you to import data from any service that exposes OData endpoint. You can either select "OData feed" in the "Get Data" window or go directly to Power Query editor and use OData.Feed function. Although documentation doesn't explain it clearly this function can operate in two different modes. You can pass either URL to the feed or complete OData query. The latter option is particularly interesting as it allows us to build dynamic queries, however, there is a price!

Let's start with a simple example that loads work items from Visual Studio Team Services using recently announced OData endpoint [2]. In the example I load all the work items that have been created in the last 7 days. Please notice that I use DateTimeZone.UtcNow function to get current date, subtract 7 days and convert it to the ISO 8601 format.

let
    since = DateTimeZone.UtcNow() - #duration(7, 0, 0, 0),
    Source = OData.Feed("https://stansw.analytics.visualstudio.com/_odata/WorkItems?" 
        & "$filter=CreatedDate lt " & DateTimeZone.ToText(since, "yyyy-MM-ddThh:mm:ssZ")
        & "&$select=WorkItemId, Title"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"WorkItemId", "Title"})
in
    #"Removed Other Columns"

This code will work great in Power BI Desktop, but when you publish it, you won't be able to schedule refresh, which defeats the purpose of publishing. Why would you share stale data?

The problem is with string concatenation. Power BI refuses to detect host for dynamic URL, thus, it cannot figure out which credentials should be applied to the dataset.

In order to fix it we need to switch to the other mode and use feed. This might not work for an arbitrarily complex OData query, but it should be sufficient for the majority of real-world queries.

This solution boils down to starting with feed and relying on Power Query feature called "folding" to translate function calls to the operations natively supported by the data source. In our case we are interested in functions Table.SelectRows and Table.SelectColumns which can be folded. Please notice that not every function can be folded! A good example is DateTimeZone.UtcNow which has to be called upfront and only its result can be passed to the filtering expression.

let
    since = DateTimeZone.UtcNow() - #duration(7, 0, 0, 0),
    Source = OData.Feed("https://stansw.analytics.visualstudio.com/_odata"),
    WorkItems_table = Source{[Name="WorkItems", Signature="table"]}[Data],
    #"Filter Rows" = Table.SelectRows(WorkItems_table, each [CreatedDate] < since),
    #"Select Columns" = Table.SelectColumns(#"Filter Rows",{"WorkItemId", "Title"})
in
    #"Select Columns"

During execution, this code will be translated to the following OData query, which is exactly what we wanted.

https://stansw.analytics.visualstudio.com/_odata/WorkItems?
  $filter=CreatedDate lt 2017-12-01T15:54:56.8747859Z
  &$select=WorkItemId,Title

If you publish the dataset to Power BI, the data source will be correctly detected and after you specify credentials you will be able to refresh the data and define a schedule.

I showed how to apply this solution to Visual Studio Team Services feed. It is, however, general and will for your feeds as well.

References

  1. Power Query M function reference - OData.Feed
  2. VSTS Analytics OData now publicly available
  3. Date and time format - ISO 8601

7 komentarzy:

  1. what if I need to add &Cross-company=true

    OdpowiedzUsuń
    Odpowiedzi
    1. With "Cross-company" column you should still be able to use Table.SelectRows function. The only problem I see is the unusual name with a hyphen, but you can handle it with an alternative column reference syntax:

      #"Filter Rows" = Table.SelectRows(WorkItems_table, each [#"Cross-company"]),

      Usuń
    2. I apologize, I should have been more clear. "Cross-Company" is not a column name. It is a way to get all data instead of just the default company. see this explanation: https://stoneridgesoftware.com/dynamics-365-for-operations-access-all-company-entity-data-from-power-bi/

      Thanks so much for your help

      Usuń
  2. Thank you very much, your article helped me a lot.

    OdpowiedzUsuń
    Odpowiedzi
    1. I'm very glad you found this post helpful. It is amazing that this post stood the test of time.

      Usuń
  3. Hi! I know the post is dated, but have you ever tried passing a parameter as part of the url?
    These queries will too refresh fine in desktop, but not in the service.

    It looks like this:


    let
    Path = "https://analytics.dev.azure.com",
    Repath = "/vfuk-digital/" & project_url & "/_odata/v3.0-preview",
    Source = OData.Feed(Path & Repath, null, [Implementation = "2.0"]),
    WorkItems_table = Source{[Name = "WorkItems", Signature = "table"]}[Data],
    #"Filtered rows" = Table.SelectRows(WorkItems_table, each [CreatedDate] > #datetimezone(2020, 2, 1, 0, 0, 0, 0, 0)),
    #"Select Columns" = Table.SelectColumns(#"Filtered rows",{"WorkItemId", "Title"})
    in
    #"Select Columns"

    Thoughts?

    OdpowiedzUsuń
    Odpowiedzi
    1. Yes, I tried, and it works great, but there is one catch. You do need to pass the URL as a single string, otherwise Power BI will consider this as a dynamic URL and refuse to refresh the dataset in the service.

      Please modify your code to this form with your real project name:

      Path = "https://analytics.dev.azure.com/vfuk-digital/MyProject/_odata/v3.0-preview",
      ...

      Usuń