Loading an image into the Model using Power Query

By | January 20, 2015

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:

image

 

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:

image

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:

image

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:

image

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:

image

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.

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:

image

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

image

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

image

  • Ronald Koster

    Hi,
    I would like to add pictures to my power view. I´ve saved images on my local C: drive. In my table in Power Pivot I`ve a column with the links to these images. The links are working when I ctrl-click them.
    Within Power Pivot advanced tab I`ve selected Table behaviour. However I cannot select a default image. [no column selected] is the option I can choose.
    Now I try to make the column binary, as described in this article, but I cannot find a way to this. I don´t have power query and unfortunately I`m not able to install it neither.
    Is there a way I can change the data type of the column to Binary within Power Pivot?
    Now I only have the option to select Data type: Auto(text), Text, Date, Decimal number, Whole number, Currency, True/False. But not Binary…
    Can somebody help because I cannot find the right information anywhere…