Excel community project rescuing the lost archive part 6

Additional visualisations

So far, the visualisations that we have used in our BI report have come from the gallery of built-in visualisations. One of the great strengths of Power BI, and one of its greatest advantages over just using Excel, is the range of additional visualisations that are available. It is also possible to create your own visualisations using R Script or Python. Given that our report is based largely on the text content of web posts, a visualisation designed around words rather than numbers would prove useful. We can search for additional visualisations by clicking on the 'Get more visuals' button in the bottom right-hand corner of the Visualizations gallery, or by clicking on the 'More visuals' command in the Insert group of the Home Ribbon tab:

This will allow the choice between adding apps from files or from the list of those made available by your organisation, or generally available apps from AppSource. We can search for apps using the Search box at the top of the left-hand pane of the Power BI Visuals screen, or browse by Category. In fact, the Word Cloud visual appears as the second item in the list of Recommended apps. The Blue tick symbol shows that the 'visual is certified by Power BI'. We can click on the visual's description to see more information:

The 'Show more' link expands the Description text while the Additional Information section includes a link to explain what the certification of the visual means. Clicking the Add button will import the visual into the report and display it underneath the gallery of built-in visuals.

This allows it to be added to any page in the same way as for a built-in visual. In our report, we have added two Word Cloud visuals – one based on our Keywords field and the other based on our Summary field. The options available for any type of visual depend on the type of visual. For a Word Cloud visual, we can just add our required text field as the Category.

The Values area allows another field to be used to determine the weighting allocated to each word which will be reflected in the size of font used. If the Values field is not used, the weighting will just be based on the frequency of occurrence of each word. The Excludes area can be used to exclude certain words based on the contents of a separate linked table but, in most cases, it is much easier just to enter Stop Words. This is one of the options available in the Format section.

You can turn Stop Words on to exclude a default list of unwanted words such as 'and' and 'the'. You can also enter specific words to be excluded. Here we have a Word Cloud based on our Summary field. This is our Word Cloud with the relative font size indicating frequency, and before turning on the Stop Words option. We have also turned off Rotate Text to make the text in the cloud easier to read:

As you can see, the cloud is dominated by words such as 'The', 'to', 'of', 'in' and 'and'. It also includes more specific words which are probably not useful in this context such as 'Excel' and 'spreadsheets'. Here is the same visual but this time with Stop Words turned on, Default Stop Words turned on and 'Excel', 'spreadsheet' and 'spreadsheets' (each separated by a space) having been entered in the Words stop list:

Because of the chart interactivity that we looked at in the previous part of the series, we can click on any one of the words in the word cloud to filter our list of articles to only include those where that word occurs within their summary field. Here is the web version of the archive article portal having clicked on 'modelling' in our Summary Word Cloud. Note that hovering over a word in the cloud displays a tip showing how many records it features in. As you can see, once we have clicked on a particular word, the other charts on the page are filtered to show relevant articles:

Resources and feedback

We have embedded the current stage of project development below:

The 'Open in full-screen mode' icon in the bottom right-hand corner of the embedded report should show the contents at a more readable size with the Escape key returning you to the post. At the moment, it is still very much a work in progress and any comments on useful ways in which we could provide access to the community content would be very welcome: excel@icaew.com.