Access to Medallia with APIs

[gdecome]

Hi team ... anyone with experience accessing Medallia system through APIs? I already have the credentials and I´m looking for some tips to expedite the process on my side. Thanks

[Anonymous]

just out of curiosity (I only know the name of Medallia), what is the reason or the usefulness to access Medallia data and process them in PQ? More precisely, is there something that can be done (or can be done more easily) in PQ and cannot be done in the same way in Medallia?

[gdecome]

I need to create a consolidate report with data from different sources (Medallia, Sales, Finance) and then I use PQ to do that

[Anonymous]

If you use sqlQuery = "describe table survey", it'll be possible to find all keys that you want 

Thank you for your post @gdecome, it was very useful

[Anonymous]

Is there a specific query to get all the "raw" data? I was looking at the medallia API guide but I just don't know how to get field by field. I'm a beginner at programming 😧

[gdecome]

There are SQL statements you can use in the query (body) to get characteristics of the tables like column names (need to investigate). Anyway, I don´t think you are able to create a query with more than 100 fields. If you need more than this in your table, need to build more than one query and combine them at the end.

[gdecome]

Great! thank you for sharing

[v-frfei-msft]

Hi @gdecome ,

To be honest, I have no experience of that. 

Maybe You can come up a new idea and add your comments there to make this feature coming sooner.

https://ideas.powerbi.com/forums/265200-power-bi-ideas

[gdecome]

Thank you ... in fact a connector would be good, but the API works. Just trying to find someone who could give some tips on how to use them.

[Anonymous]

Hi @gdecome did you find a solution for this issue?

I'm also trying to connect Medallia to Power BI. In the absence of a connector, a connection via API could be a solution like you said.

[gdecome]

Hi @Anonymous, API is working fine. See the example below. You can find more information about what to use in sqlQuery in Medallia documentation for API

1) Create a function in Power Query (e.g. fnGetData)

let

    GetData = (sqlQuery) =>   

    let

    authURL = "https://XXXXX.medallia.com/oauth/XXXXX/token",

    body = "grant_type=client_credentials&scope=-d&client_id=XXXXX&client_secret=XXXXX",

    // Get Token ----------------------------------

    getToken = Json.Document(

                    Web.Contents(

                        authURL,

                        [

                            Headers=[#"Content-Type"="application/x-www-form-urlencoded"],

                            Content=Text.ToBinary(body)

                        ]

                    )

                ),   

    token = getToken[access_token],

    // Get Data ------------------------------------   

    urlRequest = "https://XXXXX.apis.medallia.com/data/v0/mql",   

    rawData = Csv.Document(

  Web.Contents(

      urlRequest,

            [

                  Headers=[Authorization="Bearer " & token ],

                  Query=[company="XXXXX",output="csv",version="1",query=sqlQuery]

            ]

  )

  )

    in

        rawData

in

    GetData

Example of Query to use the function above

let

    sqlQuery = "SELECT COUNT(*) FROM survey",

    Source = fnGetData(sqlQuery),

    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])

in

    #"Promoted Headers"