The Power BI team just announced the latest round of new features added to Power Query in the September update. It’s really gratifying to the team’s focus on a quick tempo for updates and it certainly demonstrates the commitment to ensuring that Power Query matures into a robust tool fully capable of handling most BI-producer level data loading and transformation challenges. One of this months’ updates that jumped out at me was the addition of handling for passing binary data directly through Power Query into your Data Model.
One of the nice features of Power View is the ability to add data-bound images to a dimension which can then make attractive slicers for users consuming the dashboard. Previously, if you wanted to leverage this option, Power Pivot gave you several choices: external URLs, images in SharePoint (effectively an external URL), or storing the image in the Data Model. Microsoft has a good post on these capabilities. The benefits of including images within the model are pretty clear; namely full offline functionality. But to do so required SQL server and loading the images in as blob data (see: tutorial). Many BI-producers are not running local instances of SQL server or unwilling to hassle their DBAs and are looking for methods, via VBA or otherwise, to get the images directly from local storage into the Data Model.
Enter the September update for Power Query and we can now do this in several simple steps. I want to make a slicer of employees within the company for one of my Power View reports. I have a local folder from HR that includes all the images. Let’s get started.
Make sure to download the Power Query update and then open the tab, select From File -> From Folder.
Select the applicable folder that contains your photos
Once loaded, select the first two columns Content and Name and then Remove Other Columns. We don’t need anything else for this demo but note that the attributes array contains a ton of additional file system data that we could make use of if we needed to mine properties of the files.
Ok, let’s do some simple clean up. This assumes that your files are titled [FirstName]_[LastName].[Extension] but do whatever is applicable for your files. In this case I’ve split on the underscore delimiter to give me two columns, renamed them appropriately and then replaced the extension .jpg with no text to clean the LastName column.
Now because we’d likely want to join this to other tables in our model we might create a custom column that gives us the username.
One of things that Power Pivot requires when editing table behaviour is a unique row identifier. Hopefully we can rely on Username but it’s still good practice to throw in an index column.
Lastly, we need to make sure that Power Query identifies our Content column as Data Type Binary. This is somewhat confusing since we can clearly see the Binary entries in the column already but this step specifically tells Power Pivot how to handle this data.
Ok, give your query a name and hit Close and Load. Since we don’t need a worksheet table, select Only Create Connection and then Add this to the Data Model.
Ok, now we have our table in the data model. Click on Design -> Table Properties. We need to add the Content column since it won’t have been added by default.
With that done, go to Advanced -> Table Behavior. Fill out the fields accordingly.
Great, now we’re ready to add a power view sheet and create a tiled display!
And there you have it. We now have a rich image slicer to use in our reports. Best part is that with Power Query, this is now an automated process. Assuming I pointed this to a network share where HR was adding images as we hired employees my dashboard will always stay current with our staff list. No need for a convoluted VBA solution, no need to hassle a DBA or link to employee photos from an external site.