Images from CommCare to Power BI

Overview

This tutorial explains how images captured in CommCare can be shown on Power BI visuals with the help of data transformations on Power BI.

Step 1

Assuming that an app is already setup on CommCare with image files saved across one or multiple forms, set up the following oData feeds and exports:

OData feed for case data of case type being tracked.
OData feeds for all the forms that have an image question.
For each of these feeds, create a form export as well and export all the media to local storage.

Step 2

On Power BI, get data from the following sources:

OData for the feeds above
Folder for the folder locations where media is saved. As a best practice, save media in separate folders for the separate image captures.

Step 3

Rename queries to relevant names for the cases and form data. Go to transform data and open the advanced editor after choosing either of the folder queries where the images are stored. Edit the following in the code below and paste it.

Change folder source in line 2 as needed
Check the nomenclature of the filename in the Name column of the query and accordingly change the delimiters in the IDMatch line (line 28 below).

This code does two things:

It converts the media files into base64 image files that can be saved within the PBI database.
It creates an ID Match column to help us create a relationship between this query and the form/case feed queries from CommCare.

let
 //Get list of files in folder
 Source = Folder.Files("/path/to/files"),
 //Remove unnecessary columns
 RemoveOtherColumns = Table.SelectColumns(Source, {"Content", "Name"}),
 //Creates Splitter function
 SplitTextFunction = Splitter.SplitTextByRepeatedLengths(30000),
 //Converts table of files to list
 ListInput = Table.ToRows(RemoveOtherColumns),
 //Function to convert binary of photo to multiple
 //text values
 ConvertOneFile = (InputRow as list) =>
   let
     BinaryIn    = InputRow{0},
     FileName    = InputRow{1},
     BinaryText  = Binary.ToText(BinaryIn, BinaryEncoding.Base64),
     SplitUpText = SplitTextFunction(BinaryText),
     AddFileName = List.Transform(SplitUpText, each {FileName, _})
   in
     AddFileName,
 //Loops over all photos and calls the above function
 ConvertAllFiles = List.Transform(ListInput, each ConvertOneFile(_)),
 //Combines lists together
 CombineLists = List.Combine(ConvertAllFiles),
 //Converts results to table
 ToTable = #table(type table [Name = text, Pic = text], CombineLists),
  // Add a column to extract text between delimiters
 AddIDMatchColumn = Table.AddColumn(ToTable, "ID Match", each Text.BetweenDelimiters([Name], "rr-form_", ".jpg")),
 // Adds index column to the output table
 AddIndexColumn = Table.AddIndexColumn(AddIDMatchColumn, "Index", 0, 1, Int64.Type)
in
 AddIndexColumn

Step 4

Create relationships between the different queries:

Establish a 1 to 1 relationship between the case feed and the form feeds, mapping caseid to form case caseid.
Create a 1 to many relationship between the formid and ID Match columns in the queries with the images and their respective form queries.

See representative relationship map below.

Step 5

Navigate to the visualization section and follow these steps:

Create a measure on each of the image queries.
Use the following DAX for the measures:

Measure =
IF( HASONEVALUE('Consent Form Signatures'[Name]),
"data:image/jpeg;base64, " &
CONCATENATEX( 'Consent Form Signatures', 'Consent Form Signatures'[Pic], , 'Consent Form Signatures'[Index], ASC))

Replace the table names with the relevant ones.
Save each measure (for example, measure_signatures and measure_photos).

Step 6

Choose the table visual and add the case name from the case feed and the two measures from the images queries as separate columns. If the previous steps have been successfully completed, the table should populate the images.

Step 7

Format visuals to create different reports - the table visual is the most stable one for showing images but has formatting limitations.

Step 8

Save the local pbix file and navigate to the PBI service online. Open the workspace where the semantic model needs to be saved and upload the saved pbix file. It is easier this way rather than publishing from PBI Desktop as it can lag without any error messages.

CommCare Public