sonarQube API dynamic data

[Anonymous]

Hi,

I want to access Sonar data via the API with the following request :

let
    BaseUrl = "https://sonar.xxxx.xxx",
    RelPath = "api/projects/search",
    EntitiesPerPage = 500,

    GetJson = (Url) =>
        let options = [RelativePath=RelPath, Headers =[#"Content-type"="application/json"]],
            RawData = Web.Contents(Url, options),
            Json    = Json.Document(RawData)
        in  Json,

    Url   = BaseUrl,
    Json  = GetJson(Url),
    components = Json[components],
    #"Converti en table" = Table.FromList(components, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 développé" = Table.ExpandRecordColumn(#"Converti en table", "Column1", {"organization", "key", "name", "qualifier", "visibility", "lastAnalysisDate", "revision"}, {"Column1.organization", "Column1.key", "Column1.name", "Column1.qualifier", "Column1.visibility", "Column1.lastAnalysisDate", "Column1.revision"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Column1 développé",{"Column1.organization", "Column1.qualifier", "Column1.visibility", "Column1.lastAnalysisDate", "Column1.revision"}),
    #"Colonnes renommées" = Table.RenameColumns(#"Colonnes supprimées",{{"Column1.key", "key"}, {"Column1.name", "name"}}),
    #"Lignes triées" = Table.Sort(#"Colonnes renommées",{{"key", Order.Ascending}})
in
    #"Lignes triées"

The Data Source is used with Basic Auth and the Sonar Token in User name.

Everything works perfectly on Desktop, but when I deploy to the server and want to add the automatic refresh, I have following error :
You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

• Data source for Query1

I have a similar solution with Jira API without problems.

The Qery has no dynamic paramters. Where is the problem ?

Thanks

[v-shex-msft]

Hi @Anonymous,

If you convert custom function steps to common query table steps, did this issue appears again? If it appears again, can you please share some more detailed info to help us clarify your scenario?

How to Get Your Question Answered Quickly
Regards,

Xiaoxin Sheng

[Anonymous]

Hi,

I don't know if I understood your request correctly with "convert custom function steps to common query table steps", but I have transformed my request like this :

let
    BaseUrl = "https://sonar.xxx.xxx",
    RelPath = "api/projects/search",
    EntitiesPerPage = 500,

    GetJson = (Url) =>
        let options = [RelativePath=RelPath, Headers =[#"Content-type"="application/json"]], // pour la Basic en Base64 : token + ":"
            RawData = Web.Contents(Url, options),
            Json    = Json.Document(RawData)
        in  Json,

    Url   = BaseUrl,
    Json  = GetJson(Url),
    components = Json[components],
    #"TableConvert" = Table.FromList(components, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"TableConvert"

The problem is always the same with referesh.

I suspect the problem is with the SonarQube API. Has anyone used it in PowerBI before? Does the automatic refresh work?

Thanks

Yves

[v-shex-msft · Accepted solution]

HI @Anonymous,

I mean unpackage your custom functions, you can take a look at the following codes:

let
    BaseUrl = "https://sonar.xxx.xxx",
    RelPath = "api/projects/search",
    EntitiesPerPage = 500,
    Json =
        Web.Contents(
            BaseUrl,
            [
                RelativePath = RelPath,
                Headers = [
                    #"Content-type" = "application/json"
                ]
            ]
        ),
    components = Json.Document(Json)[components],
    #"TableConvert" =
        Table.FromList(
            components,
            Splitter.SplitByNothing(),
            null,
            null,
            ExtraValues.Error
        )
in
    #"TableConvert"

Regards,

Xiaoxin Sheng

[PaulineOL]

Hi, 

I also tried to acces Sonar data via the API in Power BI, i received the same error : 

You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

• Data source for Query1

I managed to deal with it but when i deployed to the server and set an automatic refresh, i had to update the credentials in Power BI Service. In Power BI Desktop, I can use the Basic Auth and the Sonar Token in User name without password, but in Power BI Server, i am not allowed to leave the password field empty. Do you know how i can connect?

Thank you, Pauline.