Body
Overview
The purpose of this process is to load student white coat photos to Power BI to use with reports. This will also need paired with Salesforce Contact information to connect student record information. Annually, student affairs will provide OMET with white coat photos, named with the PID and in a .jpeg format. The below process is already setup to run annually to take those jpeg images and convert them into Base64 strings which are much more user friendly and renders quicker than image loading. Everything in the Annual process has already been setup for future use, this includes the documentation of that process.
NOTE: You cannot schedule a refresh of your Power BI using a local data source, so you'll want to jump to the 'Connect to Student Photo Master Process' when using this for future Power BI projects.
Annual - White Coat Photo updates
Annually, usually in July, work with Student Affairs to obtain the white coat photos for the incoming cohort. These photos will likely need updated to work with Power BI.
- The photos MUST be .jpeg format (this format is most optimal because it is a lower file size)
- The photos MUST be named with the students PID (this is what enables connecting the image to the correct student record)
Annual - Storing White Coat Photos
There will be two places that the photos will need to be stored once the above step is completed:
- OneDrive so it is centrally stored: White Coat Photos - OneDrive
- Local PC folder, this can be a simple desktop folder (currently the script for the 'HCOM Student Roster' is using Andria Williams local folder)
- Make sure you download ALL photo images from the above OneDrive, you may need to coordinate with OMET annually over the summer when new incoming student images are loaded to the OneDrive.
Annual - Processing White Coat Photos
Once the photos are on the local location, you should be able to just refresh the Semantic model to process the new images through the python script.
- Go to the Data Reporting folder for the 'Student Photo Master'
- Open the PBI file in the desktop app
- Go to 'Transform Data' and 'Refresh All', then 'Save & Close'
- It should automatically refresh the data in the model but if not, be sure to 'Refresh'
- Once done, 'Save' and then 'Publish'
- This will publish to the Data Reporting Group and any reports that is linked to the semantic model
Initial Setup - Power BI - Connecting and transforming data
Below is the main process created to connect and transform the image data. Ideally, if someone else needs to pick this up, they should only need to grab all of the photos from the OneDrive folder, put in their local folder and just replace the path below in the existing project. The below instructions are just in case this needs rebuilt or used for a different project.
- 'Get Data'
- 'File'
- 'Folder'
- Go to the folder on your desktop that have you have images store, right click on that folder and select 'Copy as Path'
- Paste that link in the Get data dialog box OR you could browse to your desktop from the dialog box.
- Be sure to select Transform Data
- In Transform Data, you will need to complete the following steps, in order:
- Add a custom column = Table.AddColumn(Source, "Image Path", each [Folder Path] & [Name])
- Remove Other Columns, leaving just the newly created image path
- Go to Transform tab and select 'Run Python script', enter the following script, replacing the yellow highlighted section with the path to your desktop folder. The orange highlight, for the resize should work but if you are running into issues with it creating too many columns or how it looks, you may want to adjust the % accordingly up or down:
import os
import base64
import pandas as pd
from PIL import Image
image_folder = r"[UPDATE THE PATH TO THE LOCAL FILE ON YOUR PC]"
max_image_height = 250
def image_to_base64(image_path):
with open(image_path, "rb") as image_file:
return base64.b64encode(image_file.read()).decode()
def image_to_thumbnail(image_path, temp_path, max_height=250):
with Image.open(image_path) as image:
image.thumbnail([max_height, max_height], Image.Resampling.LANCZOS)
image.save(temp_path)
return
image_list = []
for file_name in os.listdir(image_folder):
if file_name.lower().endswith((".jpeg", ".jpg", ".png")):
image_path = os.path.join(image_folder, file_name)
temp_path = "resized_temp.jpg"
# Resize Image to thumbnail with max_image_height
# Thumbnail retains aspect ratio of original image
# Ideally max_image_height is small enough to have this file size be <20KB
image_to_thumbnail(image_path,temp_path,max_image_height)
# Get base64 string of resized image
base64_string = f"data:image/jpg;base64, {image_to_base64(temp_path)}"
image_list.append([file_name, base64_string])
df = pd.DataFrame(image_list, columns=["Filename", "base64"])
df
- Once that successfully runs, click on the Table next to the df line. You should see your filename and a series of columns 1 through about 30 something
- Last step to transform this data is to Add a custom column, this will pull the corresponding PID from the file name to match it to the student database later:
- = Table.AddColumn(df, "Student ID", each Text.Start([Filename],10))
- When all is completed successfully, Save & Close
Initial Setup- Troubleshooting Python
You may need command line install one or more of the functions, more than likely pandas, to do this follow these instructions:
- Go to your system search bar and type in 'Command Prompt' and open
- Then enter the following text and hit enter, everything should run on it's own
- pip install matplotlib pandas
- You may need to close down your Power BI and reopen it to run the script after this is installed
Copilot is also a great resource, if you are having issues with the code, copy/paste the code and the error, it will likely polish it up for you. Just be sure to use the copy function in the revised code it returns, don't manually select and copy it all.
To connect the student white coat images to a Power BI project, follow these steps from the project you're working on. Note that when you connect to a semantic model, you cannot update it.
- Go to 'Get Data', use the drop down and select 'Power BI semantic models'
- Search for 'Student Photo Master', select it and click 'Connect' at the bottom
- Select the table icon for 'Student Roster Images', then 'Submit'
- You should now see the Student Roster images table in your data section.
- Go to Relationships, you'll want to connect the 'PID' from your other main table source to the 'Student ID' on the Student Roster Images. This will allow you to connect the image to your dataset.
- In your visual, you'll want to use the 'Image' field from the Student Roster Table. You can then pull in the name of the student and other information from your student information table since there is now a relationship to it.
I use an installed image, Chiclet Slicer, as my image grid and it works great!
Get help from HCOM IT