Convert PowerApps Checker SARIF files to Excel using Power Query

The PowerApps Checker Powershell module introduces the ability to script the validation of solution files to analyze them for following best practices. This is an improvement over the Solution Checker which involves submitting solutions for validation through a web interface. One significant difference between the two tools is the Solution Checker makes the output available for download as Excel files whereas the PowerApps Checker outputs SARIF files.

SARIF files use the JSON file format and are meant to be consumed by other applications to display the information a human readable format. The SARIF website shows three existing tools that have been built for viewing these files, unfortunately they’re not conducive to being used in a team-based environment where the information needs to be shared, like Excel files can be.

One way to quickly convert these SARIF files to Excel is with Excel’s Power Query and its support for JSON files. After using the Power Query editor to create the query, it’s then possible to access the query in its raw text-based format so that it can be edited, or shared with others.

Here is a query that can be copied and pasted into the Power Query advanced editor to quickly convert multiple SARIF files located in one directory into a single Excel file. Note the file path where the SARIF files need to be located is on line 2.

let
    Source = Folder.Files("C:\temp\analysis\json"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Name", type text}, {"Value", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Name] = "runs")),
    Value = #"Filtered Rows"[Value],
    #"Converted to Table" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"results"}, {"Column1.results"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column2",{{"Column1.results", "result"}}),
    #"Expanded result" = Table.ExpandListColumn(#"Renamed Columns", "result"),
    #"Expanded result1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"ruleId", "ruleIndex", "level", "message", "locations", "properties"}, {"ruleId", "ruleIndex", "level", "message", "locations", "properties"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Expanded result1", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Expanded message" = Table.ExpandRecordColumn(#"Removed Blank Rows", "message", {"text", "id"}, {"message.text", "message.id"}),
    #"Expanded locations" = Table.ExpandListColumn(#"Expanded message", "locations"),
    #"Expanded locations1" = Table.ExpandRecordColumn(#"Expanded locations", "locations", {"physicalLocation", "logicalLocations", "properties"}, {"locations.physicalLocation", "locations.logicalLocations", "locations.properties"}),
    #"Expanded locations.physicalLocation" = Table.ExpandRecordColumn(#"Expanded locations1", "locations.physicalLocation", {"artifactLocation", "region"}, {"locations.physicalLocation.artifactLocation", "locations.physicalLocation.region"}),
    #"Expanded locations.physicalLocation.artifactLocation" = Table.ExpandRecordColumn(#"Expanded locations.physicalLocation", "locations.physicalLocation.artifactLocation", {"uri"}, {"locations.physicalLocation.artifactLocation.uri"}),
    #"Expanded locations.physicalLocation.region" = Table.ExpandRecordColumn(#"Expanded locations.physicalLocation.artifactLocation", "locations.physicalLocation.region", {"snippet"}, {"locations.physicalLocation.region.snippet"}),
    #"Expanded locations.physicalLocation.region.snippet" = Table.ExpandRecordColumn(#"Expanded locations.physicalLocation.region", "locations.physicalLocation.region.snippet", {"text"}, {"locations.physicalLocation.region.snippet.text"}),
    #"Expanded locations.logicalLocations" = Table.ExpandListColumn(#"Expanded locations.physicalLocation.region.snippet", "locations.logicalLocations"),
    #"Expanded locations.logicalLocations1" = Table.ExpandRecordColumn(#"Expanded locations.logicalLocations", "locations.logicalLocations", {"fullyQualifiedName"}, {"locations.logicalLocations.fullyQualifiedName"}),
    #"Expanded locations.properties" = Table.ExpandRecordColumn(#"Expanded locations.logicalLocations1", "locations.properties", {"module", "member", "type"}, {"locations.properties.module", "locations.properties.member", "locations.properties.type"}),
    #"Expanded properties" = Table.ExpandRecordColumn(#"Expanded locations.properties", "properties", {"originalSeverity", "severity", "caLevel"}, {"properties.originalSeverity", "properties.severity", "properties.caLevel"})
in
    #"Expanded properties"

If you encounter any errors with the code in the advanced query, ensure the latest version of Excel included with Office 365 is used. I have seen validation errors in this code when using the version of Excel from Office 2016.