How to Export Updatable Text to PowerPoint using R

This article describes how to create text that can automatically update into Powerpoint. This means you can add text boxes to your presentation which describe key results, and you can update these when you get your next wave of data. In this post, I will walk you through an example of how to set it up.

Requirements

A project with tables and charts you want to export to PowerPoint.
A basic understanding of How to Work with Data in R.

Method

Creating dynamic text

You can format and prepare text in Q using an R Output. Yes, there is some coding involved, but for preparing text it’s really easy!

Let’s begin with an example. I have a survey data file from a study on shoppers' supermarket preferences. One of the questions asks how likely the respondent is to recommend their main store based on the different departments (bakery, frozen foods, checkout, etc). I’ve created a table showing the average scores for each department (on a 0 – 10 scale), and I have filtered the table according to people whose main store is Woolworths. The table looks like this:

If I want to create a text box in my PowerPoint report which tells me the latest score for the bakery department at Woolworths, I can use the following:

Right-click on the table as it appears in the Report tree, and select Reference Name.
Change the reference name to something that’s easy to remember. This will make it easier to refer to this table when writing code. In this case, I change it to: woolworths.scores
Select Create > R Output.
Click into the R CODE box in the Object Inspector on the right side of the screen.
Enter the code from below.

The code for my first example is:

score = woolworths.scores["Bakery"]
woolworths.text = paste0("The average rating for the Woolworths Bakery this month is ", round(score, 1))

The initial appearance of the output in Q is:

In this code:

I refer to the table containing my scores using its Reference Name, woolworths.scores. In the screenshot below you will see that this name gets highlighted in blue, which is Q’s way of telling you that it has identified a table with the specified name.
I get the score from the Bakery row by referring to its label within the square brackets. For more on how to refer to parts of tables, see How to Work with Data in R.
I use the function called paste0() to combine the static descriptive text with the dynamic score that will change when data is updated in the table. This function is also useful when relabeling things, see How to Use R's Paste Formulas in Displayr for a plethora of examples.
I use the round() function to round the score (otherwise you’ll get about 13 decimal places!)

Formatting

You can format the text in Q, or you can format it later on once you’ve exported it to PowerPoint using the normal font settings for text. Once exported, the formatting can only be changed by changing it within PowerPoint.

Formatting in Q is done using the options in the Properties > APPEARANCE section of the Object Inspector as below. You can choose font, color, size, and a few other properties.

Exporting to PowerPoint

You can export the text to PowerPoint in just the same way you export tables and charts, using the PowerPoint icon in the toolbar. The text from my example appears like this:

From here you can:

Click into the text box and format it. Don’t edit the text itself (that is, don’t delete words or type new ones), because Q will not be able to update it once the text has changed.
Cut and paste the text box onto another page. You likely want to place the text next to charts and other results.
Resize the text box if needed.

Updating

When you get new data for your study, you can update your Q project by selecting File > Data Sets > Update, choosing the data file you are using, and then choosing your updated data file. Your table will update, and so too will the text output you have created (assuming you left the Automatic box ticked).

Then, to update the text in your PowerPoint:

Open your PowerPoint file.
Select the text output in Q.
Do the PowerPoint export again.
Click the Update button when prompted.

Updating only replaces the text, it does not change anything about formatting. This means once exported, formatting must be done in PowerPoint.

Other examples

With R, the sky is the limit. Here are a couple of examples of little extras you might like to get from your text.

Sorting and reporting the top score

This example takes the table of scores, sorts them, and reports the department with the highest score:

#identify table to use
scores = woolworths.scores
#remove the last row that's the SUM
scores = scores[1:(length(scores) - 1)]

#sort the scores in descending order
scores = sort(scores, decreasing = TRUE)
#pull off the row name with the top score
top.department = names(scores)[1]
#pull off the actual top score
top.score = scores[1]

#use the calculated top dept and score with other text to create commentary
top.department.text = paste0("The top score this month was ", 
                             round(top.score,1),
                             " for ", 
                             top.department,
                             ".")