Importing data into Power BI from Azure Marketplace

There are a number of articles on the net about importing data into Power BI from the Azure Market place, but most don’t look at the difference in importing directly from the table or using the filtered URL to drive the report. One of my wonderful colleagues had an issue with getting data into Power BI to do a mash up between footfall data and weather for a client.

So using the example UK Met Office data set in Azure Data Marketplace, you can connect to the table exposed in the Met Office set.

https://datamarket.azure.com/dataset/datagovuk/metofficeweatheropendata

Azure Data Market

So in the above image, I’ve connected to the Azure Data Marketplace using the connector in the Power BI Get Data options. That’s great, but when filtering the data in Power Query Query Editor, for example restricting the data to a date or location the filtering takes place after the importing. So Power BI will happily process a large amount of data to get to the selection you would like.

What you can to is to create the filter directly in the URL, which is like an OData import. With this data you can click on ‘Explore this data-set to generate a URL’, but if you try to put that in the M language part of the Power BI query it doesn’t like it and you get a connection error. If you use an OData connection to get the data Power BI doesn’t like it and you get a permission error, and you can’t use your log in or Azure Marketplace Key to connect. (Strangely data import in Excel will work with the URL, but its not using the Power Query part to do it)

Azure Data Market Query

You can use the URL and the OData connection to get the data, uh wait a second but you just said Power BI doesn’t like it! What are you on about?

Well the URL generated from the Azure Data Marketplace builder misses out on import bit of the URL.

What you get is:

https://api.datamarket.azure.com/DataGovUK/MetOfficeWeatherOpenData/v1/Site?$filter=ID%20eq%203

What it should be is:

https://api.datamarket.azure.com/Data.ashx/DataGovUK/MetOfficeWeatherOpenData/v1/Site?$filter=ID%20eq%203

Data.ashx is the bit missing, you need to put that in the URL and then the OData query works. Pre-Filtered and taking the heavy lifting from Power BI Desktop back to the server where it belongs.

Don’t forget you could parameterise the import in Power BI, but also don’t forget it doesn’t like columns with space in them!