Getting data from direct API call and visualize on PowerBI Dashboard

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,