Problem: We have a report system that provides encryption information of all PCs in the company, for example which PCs are encrypted and which are not, how and when they are encrypted. We use that system to see how many percent of the PC population are encrypted because there’s a KPO for that. Then we export the daily data to an Excel file and use it for the team to follow up the not-encrypted PCs, to complete the KPO. Using Excel, we can have pivot tables to see the quantity of not-encrypted PCs in each location, but we can’t see which PCs have been fixed in each location, which haven’t been fixed and which are reported as new not-encrypted PCs. I mean we can do that but it takes a lot of manual queries and macros in Excel.
So now, we’ll create a central PowerBI dashboard for the team to see the unencrypted PC status in each location, which should provide:
- Total unencrypted PCs in each country
- Details of Fixed PCs from the last report
- Details of Not fixed PCs from the last report
- Details of New unencrypted PCs from the current report
Why PowerBI: It’s the tool we have in hand, included in O365 suit (the Pro license needs to be purchased separately). It provides handy tools to work with data set like create relationship and joining tables. It provides excellent dashboard building features. And as a O365 app, it allows sharing the dashboard so much easily.
1. Get Data
Data source: Excel files exported from the local system database.
Open PowerBI Desktop and select Get data from the Wizard dialog box.
Select From File, Excel, and browse to the data file location, here I placed 2 days into 1 file as 2 sheets.
In Navigator dialog, check 2 sheets and click Load. No need to Edit the data at this time.
Then we got the data loaded. Those are PC encryption data for 2 days: 12 Aug and 4 Aug.
2. Clean up Data
In this steps, we’ll select only what we want from a big bucket of data. From Data View, select Edit Queries, then in the Edit Queries window, focus on 12Aug tab and select Choose Columns.
There are many Columns in the file, uncheck the (Select All Columns) to deselect all and only choose which Columns required for the information we need to know:
- DDay: We need it to make comparison of the statistics between days.
- Asset ID: This will be the index column
- Service Tag: Need to show in computer details
- Encrypted: Need to filter the encryption status
- slbitbuilding: Need to show in computer details
- Country Code: Need to show in computer details
Then click OK. Next, we only need information about Un-Encrypted PCs, so we’ll filter out the Encrypted column, leave only No values there. The filter may not load all the values, just click Load more to show all up.
So far we have the data we want for 12Aug table:
Perform similar steps to clean up the 4Aug table:
Don’t forget to click Close and Apply to exit the Edit Queries window.
Now the data clean up job is done, we’ll continue with Extract information step in the next part.