Power BI can't parse a JSON response from SonarQube API

[Anonymous]

Hello everybody,

I have a curious problem when querying the SonarQube API using the Web Get Data option. SonarQube is a tool used for continuous inspection of code quality. It contains various metrics related to code inspection. Let's get to the matter at hand.

I've three calls to the API looking similarly to this:

• https://<on-prem-server>/sonarqube/api/measures/component?metricKeys=ncloc,complexity,violations,blocker_violations,bugs&component=<project_id> --> this returns a json in the browser, but Power BI returns access prohibited even though I am an admin in SonarQube
• https://<on-prem-server>/sonarqube/api/components/search?qualifiers=TRK,SVW,APP --> this returns a json in the browser, and Power BI successfully parses this response allowing for further data processing
• https://<on-prem-server>/sonarqube/api/issues/search?componentKeys=<project_id> --> this returns a json in the browser, but Power BI is unable to parse the response, and only parses the top level, but the subsequent list is empty (see images)

                                                             Top level parsed

Top level parsed

                                                                  issues list is empty

Issues list is empty

Other remarks: 

• I am an admin in SonarQube in the company (and the credentials seem to be fine)
• Downloading the JSON response of the second query from web browser and manually importing it in Power BI works just fine

Does anybody know what the problem could be?

Thanks for any help!

With best regards,

Denis

[v-lili6-msft]

hi  @Anonymous 

What is your M code in your power query, I think you need "Json.Document" to parse it.

Here is a blog, you could refer to it:

https://www.thebiccountant.com/2017/08/30/how-to-open-a-complex-json-record-in-power-bi-and-power-query/

Regards,

Lin

[Anonymous]

hi @v-lili6-msft ,

my M query looks like this after trying the solution. (Json.Document was part of the original query - the original looked the same as the one below if we omit the second line in the let block )

[Anonymous]

I am sorry, the image didn't go through.

M query