Loading an image into the Model using Power Query

Last week I had an interesting questions, someone wanted to show images in Power View but didn’t want the report to have to fetch the images every time. So the images needed to be loaded into the model.

I have this simple Device table that I loaded from Azure into Power Query:

Now I want to load the image that belongs to every device, to do that I add a new column, where I load the contents from the web:

This is the code I used:

=Web.Contents(“http://www.microsoft.com/global/about/en/us/PublishingImages/About_Home_Hero.jpg?ID=” & [DeviceId])

This will retrieve the contents from the Web into M. As you might notice I put in a reference to each device as you will probably use that in most cases to find the image that you want. In my case I just loaded in the Microsoft logo.

Now this is where things get interesting, Power Query will ask you this question:

Why is it doing this? Well what we are doing here is combining information from two different datasources, we are using the DeviceId value and pass that on to the Microsoft web service as a parameter. Power Query wants you to make a conscious whether or not you allow that. When you click on continue it will ask you how you want to treat both datasources:

In my case I set both services to be Organizational as I trust them both. Just make sure you are careful when you play with these settings.

This will load the data into Power Query, the next thing we have to do is change the data type to be Binary:

Now I can close and load Power Query and load the data into the model, the data lands as Binary in Power Pivot as well. Power Pivot has also detected this field is an image.

The last thing I have to do is set the row identifier for the table that allows the Power View to display the image. It needs to have this Identifier as DAX cannot group by binary fields:

Now that we have done all this we can use the imaged in Power View:

And on the canvas you can now use the image from the website in your visuals: