Add Photos to Power BI from Active Directory

If you work for an organisation that stores thumbnail images of staff inside Active Directory, you can probably access these and incorporate the images in your Power BI Reports.  This is a great way of making an internal staff report look fantastic – and it’s easy!

Step 1. Click Get Data from the Home tab and choose “More”.

Step 2. Under “Other”, select “Active Directory”

Step 3. In the dialog, make sure that your domain name is populated.  This probably happens automatically.  Click OK.

Step 4. From the list on the left hand side, scroll down and click the checkbox by “user”, then click OK.

Step 5. (optional), Click to select the “displayName” and “organizationalPerson” columns, then right click to “Remove Other Columns”.

Step 6.  Click the expand button on the right hand side of the column header of “organizationalPerson”, in the dialog that pops up, make sure “thumbnailPhoto” is checked.  Uncheck the “Use original column names as prefix” then click OK.

Step 7. Now convert the column to text by right clicking the column header of “thumbnailPhoto” and then “Change Type” to “Text”

Step 8. We need to append some text to the start of the Base64 binary code.  To do this, highlight the “thumbnailPhoto” column and then click the “Format” button on the “Transform” tab and chose “Add Prefix”.

Step 9. In the dialog, add the following text “data:image/jpeg;base64, ” and click OK.

Step 10. Apply the updates from Power Query to Power BI Desktop.  At the very least you should see a table with two columns.  Select the “thumbnailPhoto” column and on the “Modelling” tab, change the “Data Category” to “Image URL”

Step 11. Add the “thumbnailPhoto” field to any visual that supports images (Grid, Matrix, Chicklet Slicer etc.)

And that is it!  There is some fantastic information stored in the AD data source and you can have fun extracting all sorts of useful information to enhance certain reports.  This does rely on having an updated and maintained AD structure, which is possibly out of your control.  But if this data exists in your AD setup, then you can tap into it nice and quickly – and without writing any code.

The Power Query table at step 9 could be merged into other tables using the “displayName” as a match, or you can have a fish around some of the other properties and attributes and extract these as well.  There may be email or OU type details that can help you mash the images into your other tables.

The main gotcha with this will be if the values can be updated via the personal or enterprise gateway, if you plan to publish the report to the web service and have the data refreshed.

I tweaked an idea from SQL Jasons blog to get this working, so a big thanks to Jason for laying the groundwork 🙂

Philip Seamark

Phil is Microsoft Data Platform MVP and an experienced database and business intelligence (BI) professional with a deep knowledge of the Microsoft B.I. stack along with extensive knowledge of data warehouse (DW) methodologies and enterprise data modelling. He has 25+ years experience in this field and an active member of Power BI community.