We were recently building a data model with on-prem Dynamics data mostly using the OData feed (v8.2). This article only scratches the surface of what is possible with OData (including update/write functions and much more complex queries than those shown here), but we did pick up a few tricks that were worth sharing with those just starting out.
OData is a protocol used by many web APIs and is a good way to pull data into Power BI. It isn’t the most performant way to pull data, but it is very flexible and there are some good tricks to speed things up (and it was really the only option we had).
There are two good practice OData feeds that I use in the article – Northwind and Adventureworks (links are to the root URLs). Note that OData continues to evolve and there are multiple versions out there. The latest is version 4 but you will still run into v2 and v3 out there. There are syntax and functionality differences between them, but there are good resources out there for each (here is one for v4).
The Basic Connection
To use an OData feed, just “Get Data” and choose the OData option (the “Web” option also works with OData feeds). Paste the url for the OData service and hit ok. From there, you get the option to choose the table(s) of interest and then get the query editor interface to choose and/or filter columns, perform data transformations, etc.
Note: if you take this approach and it performs well for you, you don’t need to read any further. However, if you are attempting to pull a lot of data, you may want to keep reading to learn how to modify your URL to speed things up and/or get more information about your dataset. Also, some OData sources are compatible with query folding in Power BI, but in my experience it hasn’t kicked in much. If it does work with your source, it should be equivalent to or better than the tips below. You can check with a right click on your query step(s); if “View Native Query” is not greyed out, query folding is taking place.
If you did the above and selected one of the tables, you should have a two-step query (Source and Navigation). If you delete the Navigation step you can see all the tables (“entities”) in the OData source.
Getting to Know Your Data – $count and $metadata
If you click on one of the “Table”s, you would regenerate the Navigation step to that table. Alternatively, you can click to the right of “Table” in the cell to preview the contents. To go directly to a table through your initial Source step, you just add the name of the table/entity to your URL. For example, this URL goes straight to the CompanySales table of the AdventureWorks OData feed.
If you add “/$count” to the end of the URL, you can get the number of rows in that table. While you could add a custom column that concatenates the root url, the table name, and “/$count” to get the row count for each table, a simpler way is the add a custom column with “= Table.RowCount([Data])”. Similarly, you can get the column count with “=List.Count(Table.ColumnNames([Data]))”, in which you are generating a list of the column names and then counting them. The final result is shown below for this AdventureWorks feed.
The above is just some simple table stats. If you really want to understand the data model, you can use “$metadata”, which is an XML document at the root url with lots of information, including field names/types, relationships, etc. In this case, you’d use this as your Source step and then explore through the XML.
=Xml.Tables(Web.Contents(“https://services.odata.org/V4/Northwind/Northwind.svc/$metadata”))
An easier approach is just to put the URL in quotes above in a web browser and read it in XML format. You can see all the tables, fields, keys, and relationships.
Building Your Query Faster and Decreasing Your Refresh Time – $top, $filter, $select, and $expand
Just like with other sources, if you start building queries on large data sources, you might get frustrated at the time needed to refresh the preview after each step you add. For any source, a good practice is the remove the check mark from “Allow data preview to download in the background” in the Data File section of the Options for that file/model. With OData, you can also use the $top function to limit the number of rows returned while you are building. For example, this URL returns only the top 10 rows of the WorkOrderRouting table.
https://services.OData.org/AdventureWorksV3/AdventureWorks.svc/WorkOrderRouting?$top=10
The “?” is what separates the URL and parameters you are sending in your request. Multiple parameters are separated with “&”. Now you can quickly build each step and when you are satisfied with your query, you just remove the “?$top=10” from your URL and Close&Load.
Similar with adding a SQL query for a database connection, you can move the work from your local machine to the source by adding more to the URL to your OData source. With “$filter” you can filter one or more columns on the source side before the data are returned to you, and with “$select” you can choose only the columns you want.
For example, this URL returns the seven “$select”-ed columns and only the rows that meet the two “$filter” conditions for Name and ScheduledStartDate from the WorkOrderRouting table:
https://services.OData.org/AdventureWorksV3/AdventureWorks.svc/WorkOrderRouting?$filter=Name eq ‘Final Assembly’ and ScheduledStartDate ge datetime’2008-01-01T00:00:00’&$select=WorkOrderID,ProductNumber, ProductName,Name,ScheduledStartDate,ActualStartDate,OrderQty
With an OData feed, it is also possible to pull fields from related tables by expanding those tables in the query editor. However, doing this can significantly slow things down, so you can load related tables separately or move that expand step to the source by using the “$expand” function in your URL too. You can find the syntax for all of the functions above and the many other things possible with OData online. Here is one good resource. Note that you should look at the document for the OData version provided in your source.
Note: Typing all the columns names exactly right in your $select string can take a while. You can use the query editor to make it easier to build more complex URLs. For example, you can use Text.Combine(Table.ColumnNames(RemoveOtherColumnsQueryStep)) to make a text string of your selected column names from an earlier query step that you can concatenate with the rest of the URL. That way, if you edit your “Remove Other Columns” step in the future, you won’t need to retype the list of columns names exactly. Sadly, I wasn’t able to figure out how to get the table name from the Navigation step or the column names and types earlier M steps, so couldn’t generate the entire URL string easily (one could save the M code as text file and bring it in that way, but that’s probably not worth it).
Bonus Tips For Two Web API Scenarios
Usually with an OData source, all the data will be returned with a single URL call (even though it may take a while). You may run into web APIs where only a fixed number of rows are returned per call (e.g., 5000) or you may be limited to a certain number of calls per minute (e.g., free account vs. premium account to a stock price history API).
Fixed Number Of Rows Per Call
The “$skip” function allows you to skip a set number of rows before your data are returned. With a little bit of M code and a function, you can still get all the data. The trick is to first make a list the increments by the row limit of your source and then make URL using each value as the “$skip” amount. Here is some example M code:
let
fnGetData= (skiptoken)=> OData.Feed(“https://services.OData.org/AdventureWorksV3/AdventureWorks.svc/WorkOrderRouting?$top=10000&$skip=”&skiptoken, null, [Implementation=”2.0”]), //define a function to make the calls
Source = List.Numbers(0,7,10000), //make a list that increments by 10000 rows (0, 10000, 20000, …)
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), //make it a table
#”Changed Type” = Table.TransformColumnTypes(#”Converted to Table”,{{“Column1”, type text}}),
#”Renamed Columns” = Table.RenameColumns(#”Changed Type”,{{“Column1”, “skipnumber”}}),
#”Added Custom” = Table.AddColumn(#”Renamed Columns”, “Data”, each fnGetData([skipnumber])), //invoke your custom function
#”Expanded Data” = Table.ExpandTableColumn(#”Added Custom”, “Data”, {“WorkOrderID”, “ProductNumber”, “ProductName”, “Name”, “ScheduledStartDate”, “ActualStartDate”, “OrderQty”}, {“WorkOrderID”, “ProductNumber”, “ProductName”, “Name”, “ScheduledStartDate”, “ActualStartDate”, “OrderQty”}) //expand the desired columns and append all the tables
in
#”Expanded Data”
Note: the “$top” in the Url above is not needed. I only added it to simulate a data feed that limits you to only 10000 rows.
Fixed Frequency Of Calls
This one is less common, but you may see it if you are are like me and try to get the most out of the free version of accounts. Pretend you would like to look up the stock history for 10 stock symbols but your free web API only lets you make 6 calls/minute. Power BI is faster than that, so you will get an error if you call >6. With a little more M trickery (thanks to Chris Webb [link removed due to 404] here), you can use the Function.InvokeAfter() function to wait a set amount before each call.
The M code is the same as the one above, but replace the step with the custom function as below:
#”Added Custom” = Table.AddColumn(#”Renamed Columns”, “Data”, each Function.InvokeAfter(()=>fnGetData([skipnumber]), #duration(0,0,0,10))),
The first clause of the Function.InvokeAfter function is a function (here we are defining a new function from the fnGetData function operating on the specified skipnumber column), and the second is the desired time to wait (in this case the #duration is set to 10 sec before invoking the function). Of course this adds to your refresh time, but may be worth it in some cases.
I hope some of these tips save you time. Check out this post if you want to measure how long your refresh is taking.
Where It’s At: The Intersection of Biz, Human, and Tech*
We “give away” business-value-creating and escape-the-box-inspiring content like this article in part to show you that we’re not your average “tools” consulting firm. We’re sharp on the toolset for sure, but also on what makes businesses AND human beings “go.”
In three days’ time imagine what we can do for your bottom line. You should seriously consider finding out 🙂
* – unless, of course, you have two turntables and a microphone. We hear a lot of things are also located there.
