{"id":231022,"date":"2022-10-27T18:30:00","date_gmt":"2022-10-27T17:30:00","guid":{"rendered":"https:\/\/www.virtualworlduk.co.uk\/wordp\/?p=231022"},"modified":"2022-10-27T14:18:36","modified_gmt":"2022-10-27T13:18:36","slug":"skyline-basic-power-bi-report-part-2","status":"publish","type":"post","link":"https:\/\/www.virtualworlduk.co.uk\/wordp\/skyline-basic-power-bi-report-part-2\/","title":{"rendered":"Skyline \u2013 Basic Power BI Report \u2013 Part 2"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

As you may recall, we’re part way through creating a Power BI report to display information collected from VMware Skyline.\u00a0 The first part can be found here:\u00a0\u00a0https:\/\/www.virtualworlduk.co.uk\/wordp\/?p=230985<\/a><\/p>

In Part 1, we had downloaded the export from Skyline, and created some of the queries that gather the data from the exports ready to be used in the report components.\u00a0 This, primarily, resulted in us having the ‘LatestFile’ table available to be used in the Power BI designer page.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

Two Additional Queries<\/h4>

To enable us to have some of the capabilities to show how the number of findings are changing over time, we need have a query to count the number of findings across each of the export files in our folder… this will be handled with one query.\u00a0 The second query will take the total number of findings that we enter as a parameter and utilise that on the page but then also utilise that information to help calculate the percentages used on the other charts.<\/p>

Findings Count Query<\/h5>
To save some time we’ll utilise the Advanced Editor to add in the details for this query.\u00a0 In essence, the query is loading up each of the files in our export folder, counting the number of findings and then putting the count of numbers alongside the file date for the export.\u00a0 As more export files are added to the folder, this will add more lines to the table each with the date when the file was exported and the number of findings for that file… allowing us to use that information to create a line chart for time and number.\u00a0
To start this off we need to click the ‘Transform Data’ link at the top of the screen to go into the data transformation screen.<\/div>
From here, we can click the ‘New Source’ arrow and then select ‘Blank Query’<\/div>
With the query created, right mouse click on the query and left click on ‘Rename’ – rename the query to be called:\u00a0 FindingsCount<\/div>
Right mouse click on the FindingsCount query and then left click on ‘Advanced Editor’.\u00a0 This will now open up the advanced editor that we have used before.<\/div>
\u00a0<\/div>
Paste the following code into the advanced editor replacing all of the code that exists in there already:<\/div>
let
Source = Folder.Files(FileLocation),
#\"Filtered Rows\" = Table.SelectRows(Source, each ([Extension] = \".csv\")),
#\"Sorted Rows\" = Table.Sort(#\"Filtered Rows\",{{\"Date created\", Order.Descending}}),
#\"Kept First Rows\" = Table.FirstN(#\"Sorted Rows\",6),
#\"Filtered Hidden Files1\" = Table.SelectRows(#\"Kept First Rows\", each [Attributes]?[Hidden]? <> true),
#\"Invoke Custom Function1\" = Table.AddColumn(#\"Filtered Hidden Files1\", \"Transform File\", each #\"Transform File\"([Content])),
#\"Renamed Columns1\" = Table.RenameColumns(#\"Invoke Custom Function1\", {\"Name\", \"Source.Name\"}),
#\"Removed Other Columns1\" = Table.SelectColumns(#\"Renamed Columns1\", {\"Source.Name\", \"Transform File\"}),
#\"Expanded Table Column1\" = Table.ExpandTableColumn(#\"Removed Other Columns1\", \"Transform File\", Table.ColumnNames(#\"Transform File\"(#\"Sample File\"))),
#\"Filtered Rows1\" = Table.SelectRows(#\"Expanded Table Column1\", each ([Issue Description] <> \"\")),
#\"Changed Type\" = Table.TransformColumnTypes(#\"Filtered Rows1\",{{\"Source.Name\", type text}, {\"Finding Id\", type text}, {\"Issue Description\", type text}, {\"Severity\", type text}, {\"Number of Affected Objects\", Int64.Type}, {\"First Observed\", type date}, {\"Risk if no Action Taken\", type text}, {\"Recommendations\", type text}, {\"Reference\", type text}}),
#\"Split Column by Position\" = Table.SplitColumn(#\"Changed Type\", \"Source.Name\", Splitter.SplitTextByPositions({0, 14}, true), {\"Source.Name.1\", \"Source.Name.2\"}),
#\"Changed Type1\" = Table.TransformColumnTypes(#\"Split Column by Position\",{{\"Source.Name.1\", type text}, {\"Source.Name.2\", type text}}),
#\"Split Column by Position1\" = Table.SplitColumn(#\"Changed Type1\", \"Source.Name.2\", Splitter.SplitTextByPositions({0, 10}, false), {\"Source.Name.2.1\", \"Source.Name.2.2\"}),
#\"Renamed Columns2\" = Table.RenameColumns(#\"Split Column by Position1\",{{\"Source.Name.2.1\", \"Date\"}}),
#\"Split Column by Delimiter\" = Table.SplitColumn(#\"Renamed Columns2\", \"Date\", Splitter.SplitTextByDelimiter(\"-\", QuoteStyle.Csv), {\"Date.1\", \"Date.2\", \"Date.3\"}),
#\"Reordered Columns\" = Table.ReorderColumns(#\"Split Column by Delimiter\",{\"Source.Name.1\", \"Date.2\", \"Date.1\", \"Date.3\", \"Source.Name.2.2\", \"Finding Id\", \"Issue Description\", \"Severity\", \"Number of Affected Objects\", \"First Observed\", \"Risk if no Action Taken\", \"Recommendations\", \"Reference\"}),
#\"Merged Columns\" = Table.CombineColumns(#\"Reordered Columns\",{\"Date.2\", \"Date.1\", \"Date.3\"},Combiner.CombineTextByDelimiter(\"-\", QuoteStyle.None),\"Merged\"),
#\"Changed Type2\" = Table.TransformColumnTypes(#\"Merged Columns\",{{\"Merged\", type date}, {\"Source.Name.2.2\", type text}}),
#\"Renamed Columns\" = Table.RenameColumns(#\"Changed Type2\",{{\"Merged\", \"Date\"}}),
#\"Added Index\" = Table.AddIndexColumn(#\"Renamed Columns\", \"Index\", 1, 1, Int64.Type),
#\"Grouped Rows\" = Table.Group(#\"Added Index\", {\"Date\"}, {{\"Count\", each Table.RowCount(_), Int64.Type}})
in
#\"Grouped Rows\"<\/pre>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
Once this is done, click the ‘Done’ button and the code will be added to the query.\u00a0 The result of the query should be a column showing the Date and a column showing the count of findings.<\/p>
Total Findings Query<\/h5>
The last of the queries that we need to create is the one to show the total number of findings as a numerical figure that we can then use in calculations later.<\/p>
Create a new query, rename the query to be:\u00a0 TotalFindings<\/p>
Right mouse click on the TotalFindings query and left click on ‘Advanced Editor’<\/p>
Paste the following code into the advanced editor to create the TotalFindings query:<\/p>
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(\"i45WMjUzV4qNBQA=\", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#\"Total Findings\" = _t]),
#\"Changed Type\" = Table.TransformColumnTypes(Source,{{\"Total Findings\", Int64.Type}}),
#\"Renamed Columns\" = Table.RenameColumns(#\"Changed Type\",{{\"Total Findings\", \"Old Total Findings\"}}),
#\"Removed Columns\" = Table.RemoveColumns(#\"Renamed Columns\",{\"Old Total Findings\"}),
#\"Added Custom\" = Table.AddColumn(#\"Removed Columns\", \"TotalFindings\", each {#\"Number of Findings\"}),
#\"Expanded TotalFindings\" = Table.ExpandListColumn(#\"Added Custom\", \"TotalFindings\")
in
#\"Expanded TotalFindings\"<\/pre>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
After clicking Done, the TotalFindings query should show a column named ‘TotalFindings’ with a figure underneath that matches the figure entered for the Number of Findings parameter we entered earlier.<\/p>
You should now be able to click the ‘Close & Apply’ button to have the new queries transferred into the design space.<\/p>
You should now see the following under the ‘Fields’ view:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
Creating Measures<\/h4>
Although it seems like we are having to do a lot of prep work before getting into creating the actual report, we are laying the foundations to the report we want to create.<\/p>
During the initial creation of this report, I had a rough idea of what I wanted to create and would create some visuals on the report and then make the measures and queries to match what I wanted to show.\u00a0 For this walkthrough, I’ve adjusted the order slightly to get most of the items created up front and then the final stage is to create the visuals.<\/p>
The majority of the measures we are going to create are going to be added to the ‘TotalFindings’ query, as they are calculations taken from that table to help show percentages in pie charts and coloured sections of the pie charts.<\/p>
To create a measure, you should move your mouse over the ‘TotalFindings’ query under the ‘Fields’ area and then click on the ‘…’ (three dots at the right of the name), this will open a menu and you should then click ‘New Measure’.\u00a0 This will create a new code entry box at the top of the report window.\u00a0 Enter a piece of code below, press enter to accept the code, then create a new measure before entering the next piece of code.<\/p>
%AllFindings =

VAR __TotalFindings = ('TotalFindings'[Total Findings])

VAR __FindingCount = COUNT('LatestFile'[Severity])

VAR __Remainder = __TotalFindings-__FindingCount

RETURN FORMAT(DIVIDE(__Remainder,__TotalFindings),\"0.00%\")<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
Throughout the code entry in the measures both above and below, do not be concerned if you receive an error.\u00a0 This is just that it is calling on a measure that you haven’t created yet.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
%Critical = FORMAT(DIVIDE(COUNTROWS(FILTER('LatestFile','LatestFile'[Severity]=\"CRITICAL\")),COUNTROWS('LatestFile')),\"0.00%\")<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
%Moderate = FORMAT(DIVIDE(COUNTROWS(FILTER('LatestFile','LatestFile'[Severity]=\"MODERATE\")),COUNTROWS('LatestFile')),\"0.00%\")<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
%TotalFindings = FORMAT(DIVIDE(COUNT('LatestFile'[Severity]),'TotalFindings'[Total Findings]),\"0.00%\")<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
%Trivial = FORMAT(DIVIDE(COUNTROWS(FILTER('LatestFile','LatestFile'[Severity]=\"TRIVIAL\")),COUNTROWS('LatestFile')),\"0.00%\")<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
Critical = COUNTROWS(FILTER('LatestFile','LatestFile'[Severity]=\"CRITICAL\"))<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
CriticalLeft = COUNT('LatestFile'[Severity])-[Critical]<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
Moderate = COUNTROWS(FILTER('LatestFile','LatestFile'[Severity]=\"MODERATE\"))<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
ModerateLeft = COUNT('LatestFile'[Severity])-[Moderate]<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
Other Findings = 'TotalFindings'[Total Findings]-COUNT('LatestFile'[Severity])<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
Total Findings = CALCULATE(MAX('TotalFindings'[TotalFindings]))<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
Trivial = COUNTROWS(FILTER('LatestFile','LatestFile'[Severity]=\"TRIVIAL\"))<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
TrivialLeft = COUNT('LatestFile'[Severity])-[Trivial]<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
The final measure to create is one on the ‘FindingsCount’ query.\u00a0 Click the ‘…’ next to the FindingsCount name in the ‘Fields’ area of the screen and then left click on ‘New Measure’.\u00a0 Enter the following code for that measure in the measure box:<\/p>
CountItems = COUNT(FindingsCount[Date].[Date])<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
There is one additional item that we need to add and this is a column.\u00a0 This is performed in much the same way as creating a measure except this will add a column to query based on a calculation rather than a single entry.<\/p>
We’ll be adding this column to the ‘TotalFindings’ query and therefore click the ‘…’ next to the TotalFindings name in the ‘Fields’ area of the screen and then left click on ‘New Column’.\u00a0 A code box will appear in the same place as the measure code box would normally appear, you should enter the following code into that box:<\/p>
Your Findings = COUNT('LatestFile'[Severity])<\/pre><\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
After all of this work you should have something similar to below:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
Now we are ready to create some visuals.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
Part 3 of this walkthrough continues at the following link:<\/p>
 https:\/\/www.virtualworlduk.co.uk\/wordp\/?p=231032<\/a><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"
As you may recall, we’re part way through creating a Power BI report to display information collected from VMware Skyline.  The first part can be found here:  https:\/\/www.virtualworlduk.co.uk\/wordp\/?p=230985 In Part 1, we had downloaded the export from Skyline, and created some of the queries that gather the data from the exports ready to be used in the report components.  This, primarily, […]<\/p>\n","protected":false},"author":1,"featured_media":230988,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[9],"tags":[],"class_list":["post-231022","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-vmware"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.virtualworlduk.co.uk\/wordp\/wp-content\/uploads\/2022\/10\/2022-10-21-15_22_05-Skyline-Insights-1.0-Community-Power-BI-Desktop.png","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.virtualworlduk.co.uk\/wordp\/wp-json\/wp\/v2\/posts\/231022","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.virtualworlduk.co.uk\/wordp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.virtualworlduk.co.uk\/wordp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.virtualworlduk.co.uk\/wordp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.virtualworlduk.co.uk\/wordp\/wp-json\/wp\/v2\/comments?post=231022"}],"version-history":[{"count":8,"href":"https:\/\/www.virtualworlduk.co.uk\/wordp\/wp-json\/wp\/v2\/posts\/231022\/revisions"}],"predecessor-version":[{"id":231080,"href":"https:\/\/www.virtualworlduk.co.uk\/wordp\/wp-json\/wp\/v2\/posts\/231022\/revisions\/231080"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.virtualworlduk.co.uk\/wordp\/wp-json\/wp\/v2\/media\/230988"}],"wp:attachment":[{"href":"https:\/\/www.virtualworlduk.co.uk\/wordp\/wp-json\/wp\/v2\/media?parent=231022"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.virtualworlduk.co.uk\/wordp\/wp-json\/wp\/v2\/categories?post=231022"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.virtualworlduk.co.uk\/wordp\/wp-json\/wp\/v2\/tags?post=231022"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}