Connecting MS Secure Scores to Power Query

Hey, might be a little late to the party but i have been sitting with the same project and I've found a solution that works great. 

First you would need to create a app registration in azure with the api permissions SecurityEvents.Read.All.

Create a Client Secret for the app in Azure.

In Power BI Desktop create Parameters for

"TenantID" = The Tenant ID in Azure

"resourceGraphUri" = https://graph.microsoft.com/

"AppID" = Application (client) ID for the app in Azure

"AppSecret" = Client Secret Value for the app in Azure

"clientCredentials" = client_credentials

"secureScoresUri" = https://graph.microsoft.com/beta/security/secureScores?$top=1

"secureScoreControlProfileUri" = https://graph.microsoft.com/beta/security/secureScoreControlProfiles?$top=395

Create a blank query named AccessToken, open Advanced Editor. Paste following code:

let
    //POST Request
    Url = "https://login.microsoftonline.com/"&TenantId&"/oauth2/token",
    Body = "resource="&resourceGraphUri&"&client_id="&AppId&"&client_secret="&AppSecret&"&grant_type="&clientCredentials,
    Options = [
        Content = Text.ToBinary(Body)
    ],

    //Get token
    Response = Web.Contents(Url,Options),
    ParsedJson = Json.Document(Response),
    ConvertedToken = "Bearer "&ParsedJson[access_token]
in
    ConvertedToken

Save the query and create another blank query named what ever you'd like. Open Advanced Editor and paste following code:

let
    AuthHeader = #"AccessToken",
    Options = [
        Headers = [
            #"Authorization" = AuthHeader,
            #"Content-Type" = "Application/Json"
        ]
    ],
    WebRequestContent = Web.Contents(secureScoresUri,Options),
    JsonContent = Json.Document(WebRequestContent),
    ParsedResults = JsonContent[value],
    Converted = Table.FromList(ParsedResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(Converted,"Column1",
                    Record.FieldNames(Converted[Column1]{0}),
                    Record.FieldNames(Converted[Column1]{0})),
    #"Changed Type" = Table.TransformColumnTypes(Expanded,{{"activeUserCount", Int64.Type}, {"currentScore", type number}, {"maxScore", type number}})
in
    #"Changed Type"

Save the query. This will get you all secure score details for the tenant. 

If you'd like to get all the details for each controlScore like Category as you mentioned. Create another blank query and paste following code:

let
    AuthHeader = #"AccessToken",
    Options = [
        Headers = [
            #"Authorization" = AuthHeader,
            #"Content-Type" = "Application/Json"
        ]
    ],
    WebRequestContent = Web.Contents(secureScoresUri,Options),
    JsonContent = Json.Document(WebRequestContent),
    ParsedResults = JsonContent[value],
    Converted = Table.FromList(ParsedResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(Converted,"Column1",
                    Record.FieldNames(Converted[Column1]{0}),
                    Record.FieldNames(Converted[Column1]{0})),
    controlScores1 = Expanded[controlScores],
    controlScores2 = controlScores1{0},
    #"Converted to Table" = Table.FromList(controlScores2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"controlCategory", "controlName", "description", "score", "lastSynced", "implementationStatus", "on", "scoreInPercentage"}, {"controlCategory", "controlName", "description", "score", "lastSynced", "implementationStatus", "on", "scoreInPercentage"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Column1",{{"controlName", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"score", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DescriptionFormatted", each if [description] = null then null else   Html.Table([description] , {{"text",":root"}})),
    #"Expanded DescriptionFormatted" = Table.ExpandTableColumn(#"Added Custom", "DescriptionFormatted", {"text"}, {"DescriptionFormatted.text"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded DescriptionFormatted", "implementationStatusFormatted", each if [implementationStatus] = null then null else   Html.Table([implementationStatus] , {{"text",":root"}})),
    #"Expanded implementationStatusFormatted" = Table.ExpandTableColumn(#"Added Custom1", "implementationStatusFormatted", {"text"}, {"implementationStatusFormatted.text"})
in
    #"Expanded implementationStatusFormatted"

Hope this helped. 🙂