Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

sonarQube API dynamic data

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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