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. NOTE: You cannot schedule a refresh of your Power BI using a local data source.
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)
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.
Power BI - Connecting and transforming data
If you are creating a new Power BI project that you want to link these images to follow these steps:
- '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
# Function to convert an image file to base64
def image_to_base64(image_path):
with open(image_path, "rb") as image_file:
base64_data = base64.b64encode(image_file.read()).decode()
return base64_data
def resize_image(image_path, percentage):
img = Image.open(image_path)
width, height = img.size
new_width = int(width * percentage / 100)
new_height = int(height * percentage / 100)
resized_img = img.resize((new_width, new_height), Image.LANCZOS)
return resized_img
# Corrected image folder path
image_folder = r"C:\Users\williaa4\OneDrive - Ohio University\Desktop\Student Roster images"
# Initialize an empty DataFrame
df = pd.DataFrame(columns=["Filename"])
# Iterate through the image files in the folder
for filename in os.listdir(image_folder):
if filename.lower().endswith((".jpeg", ".png", ".jpg")):
image_path = os.path.join(image_folder, filename)
# Resize the image by 50%
resized_image = resize_image(image_path, 50)
# Save the resized image temporarily
temp_path = "resized_temp.jpeg"
resized_image.save(temp_path)
# Convert resized image to base64
base64_data = image_to_base64(temp_path)
# Split the base64 data into chunks of 32,000 characters
base64_chunks = [base64_data[i:i + 32000] for i in range(0, len(base64_data), 32000)]
# Create a dictionary to represent the row
row_dict = {"Filename": filename}
for i, chunk in enumerate(base64_chunks):
row_dict[f"C{i+1}"] = chunk
# Append the data to the DataFrame
df = pd.concat([df, pd.DataFrame([row_dict])], ignore_index=True)
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
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.
Power BI Visual formatting
Now that you have your images connected and transformed, there are just a few additional steps before you can use the images in a visual.
- On the far right side, under the data section, click on the table for the images, then 'Column Tools' at the top, select 'New Column'
- Use the following string to translate those columns into the new image link to use. The number of columns may vary but should not exceed 60 or it will go over your character max allowed. Add/remove from below as needed. In the table view, you can see how many columns there are.
- Image = "data:image/png;base64," & [C1] & [C2] & [C3] & [C4] & [C5] & [C6] & [C7] & [C8] & [C9] & [C10] & [C11] & [C12] & [C13] & [C14] & [C15] & [C16] & [C17] & [C18] & [C19] & [C20] & [C21] & [C22] & [C23] & [C24] & [C25] & [C26] & [C27] & [C28] & [C29] & [C30] & [C31]
- Once the above is completed successfully, go to the relationship view on the left, select the table for the images. On the far right, mass select all of the C1 through C31 columns, then in the properties update 'Is hidden' to yes. This will hide all of those individual columns.
- While in the relationship view, you'll also want to connect your student data table (whether this is Salesforce, eMedley data, etc.). Connect the 'Student ID' in the image table to whatever PID you have in the student contact data source.
- Now, you should be able to use the 'Image' field along with your contact data with visuals.
Get help from HCOM IT