PowerBI Desktop 50

Published on October 27th, 2015 | by Fredrik Knalstad


From Email to PowerBI desktop Chart in 5 minutes

The other day I got a question regarding counting huge amount of Alert Emails from System Center Operation Manager (SCOM). SCOM had sent out large amount of Alert Emails to an account and in every email there was a line in the Description that showed the automation job that had failed.

The Image bellow shows one of the emails and job that had failed (“Source:”)

The question was then ” How can we count the different jobs to see which are failing the most?”
To solve this we need to extract that info from about 400 emails and count the different lines.


Well we solved this in less than 5 minutes, with a little help from Power BI Desktop. Here was the process:

In outlook open a new message and select [Attach Item] then [Outlook Item]


Select all the Emails and insert the items as “Text Only”, this will add all the selected emails to the new message as plain text.


Now I got one huge email with a lot of unstructured information, save the entire mail as a text document.
Open up Power BI Desktop, Click on [Get-Data] and select [Text]


Browse to the location of the text file and open it up in Power BI Designer.


Now we have imported the text file it’s time to filter the data we need. Click on [Edit].











In the Power BI Designer Editor, select [Add Column] -> [Duplicate Column].


Now we got two identical columns with the same information.


Select the new copy column and on the [Home] tab, select [Group by].


We are now going to do a count on the values in the “Column1 – Copy” and we are going to select the operation “Count Rows”.
Then add a name that represent the new values for the column.


We can now see that every unique line has been counted in the new column.
This is great, now we need to filter this down to the information that we want.


The information we want starts with Source, so we need to remove all the lines that don’t start with Source. In the “Activity Line” column, select the drop down arrow to open up the filter and Sort options.


In the text filter area, add “Source:” this will select only the unique lines that start with Source. This is exactly what we need. Click [OK].


Now we only have the unique activity lines and count of how many times the alarm has been triggered.


To create a visual out of this data, select [Close & Apply].


In the Report area, select both the fields, the we will get an overview of all the activities.


We only want to view the alerts that has failed more than 10 times over this period, so we need to add a filter to the Count of Lines column. In the filters area, select the “Count of Lines” -> “is greater than or equal to” “10“. Then click [Apply Filter].


After the filter is applied click on [Column Chart], to get a graphical overview of the results.50

Next step is to upload the report to PowerBI.com and share the report with the team. All done in less than 5 minutes.


About the Author

assist companies in deploying, automating and designing management solutions based on Datacenter | Intelligence | Automation. The latest years I have been working deeply with System Center and developing integrations with systems outside of System Center.This includes planning, design, implementation and using self-service Business Intelligence.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to Top ↑