Getting data from direct API call and visualize on PowerBI Dashboard

[Anonymous]

Hi There ! 

I'm building a PowerBI online Dashboard which is visualizing data that can be retrieved from a direct API call for my backend system with an API endpoint like this:

(GET) https://api.testapi.online/api/v1/admin/usageadmin/unreported/{program}/{year}/{month}

In the above API endpoint, I have three dynamic (program, year, month) variables that a user can change from the dashboard dropdown menu and then visualize the data. In this case, I can't retrieve the data and store in another database are these data is realtime (dynamically change time to time).

How can I build a dashboard to a use case like this?  Thanks in advance 🙂

[v-lid-msft]

Hi @Anonymous ,

Sorry for that, but currently the Web Connector does not support Directquery, we can generate a pre-table to get all possible result, then use scheduled refresh to make it update by hours.

Example query are here:

let
  TOKENS_TRING = "XXXXXXXXXXXXXXXXXXXXX",

  // generate all the possible ProgramNames/Years/Month
  ProgramsTable = Table.FromRows(
    {
        {"ProgramA"},
        {"ProgramB"},
        {"ProgramC"}
    },
    {"ProgramName"}
  ),
  YearTable = Table.ExpandListColumn(Table.AddColumn(ProgramsTable,"Year",each {1990..2021}), "Year"),
  MonthTable = Table.ExpandListColumn(Table.AddColumn(YearTable,"Month",each {1..12}), "Month"),

  // Call APi for each program/Year/Month
  Source = Table.AddCoulmn(MonthTable,"Data", each 
    let
        // configure string
        URL_STRING =  "https://api.testapi.online/api/v1/admin/usageadmin/unreported/",
        RELATIVE_PATH_STRING = [ProgramName] & "/" & [Year] & "/" & [Month],
        TIMEOUT_VALUE = #duration(0,0,5,0),
        HEADER_RECOEDS=[Authorization="Bearer " & TOKENS_TRING]
    in
        Web.Contents(URL_STRING,[RelativePath = RELATIVE_PATH_STRING, Timeout = TIMEOUT_VALUE, Headers = HEADER_RECOEDS])
  )

in
  Source

Then expand the [Data] Column to get desire data, you can apply slicer/filters to get filted data

Or you can try to create a ODBC custom connector to call the RESTful API, the ODBC custom connector can support direct query and achieve a near-realtime report experience, please refer to following documents about how to create a custom connector:

https://docs.microsoft.com/en-us/power-query/startingtodevelopcustomconnectors

https://docs.microsoft.com/en-us/power-query/odbc#odbc-extensibility-functions

Best regards,

[Anonymous]

Thanks for the reply @v-lid-msft  : Can we dynamically change the API URL properties in the above way - for example, if the user selects the Month and year from a drop-down in the PowerBI Dashboard - at that time how can we call the API happening the values given by the user via drop-down ?

[v-lid-msft]

Hi @Anonymous ,

Sorry for that, Based on my research, We did not found an effective solution to meet your requirement, if we use query parameter to change the query, we still need to refresh the dataset to get the update data depends on the parameter. Maybe @ImkeF can give us a brilliant idea or proper way guide?

Best regards,

[Anonymous]

Thanks for the reply @v-lid-msft : @ImkeF May I seek your support if you know a solution for this Thanks in advance 🙂