How to Get Data From Web Application into Power BI

[Anonymous]

HI All

I am working for a business that uses a piece of booking software called "Nookal", which is used to manage the booking and invoicing system for medical practices (i.e. physio's, dentists etc,). I want access the data from it and pull it all across into Power BI, all I have from Nookal is an API Key.

How do you get it from an API key into a set of queries inside Power BI?

Thanks

[v-shex-msft]

HI @Anonymous,

In my opinion, I'd like to suggest you contact software support to get API developer documents, then you can use 'Web.connector' to get data from their API to do analysis to their data.

Reference link: use Web.connector to get data from Rest API

Pull data from a REST API Authentication 

Otherwise, you need to use fiddler and debug mode of your web browser to trace these requests to manually research parameters of these requests and correspond results and data structure. (Notice: it is tough and complex to do these if you not familiar with API develop and debug)

BTW, if you only want to scrap some data from the web site, you can use web.connector with 'Extract table using examples' option:

Get data from a Web page by providing an example 

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks Xiaoxin

So heres an update. The link of the API document is:

https://api.nookal.com/developers

To me it doesn't seem as easy as using web.connector with the API. Not sure if with this one you'd have the write a new connector for it.

I'm not trying to scrape the data from the website, I really want to pull the data behind some of the reports into Power BI

Thanks for the help

[v-shex-msft]

HI @Anonymous ,

Please check the following custom functions if it is suitable for your requirements. (notice: I do not have vailed apikey to test, so you need to modify following code if it structure not suitable for its develop document)

Custom function:

let
    InvokeApi=(apikey as text, relativepath as text,optional content as text) =>
    let
        rooturl = "https://api.nookal.com",
        AccessTokenHeader = apikey,
        GetJsonQuery =
        if 
            content=null 
        then 
            Web.Contents(rooturl,
                [
                    Headers = [#"apiKey"=AccessTokenHeader,
                    #"Content-Type"="application/x-www-form-urlencoded; charset=UTF-8"],
                    RelativePath=relativepath
                ]
            )
        else
            Web.Contents(rooturl,
            [
                Headers = [#"apiKey"=AccessTokenHeader,
                #"Content-Type"="application/x-www-form-urlencoded; charset=UTF-8"],
                RelativePath=relativepath,
                Content = Text.ToBinary(content) 
            ]
        )
        ,
        Result = Binary.From(GetJsonQuery)
    in
        Result
 in
    InvokeApi

ApiDictionary:

let
    Source =  [      
        //Basic Verification Tools
        version                   = "/production/v2/version",
        verify                    = "/production/v2/verify",
        //Basic Retrieval tools (locations, practitioners)
        locations                 = "/production/v2/getLocations",
        practitioners             = "/production/v2/getPractitioners",
        //Retrieve Services/Stock/Inventory elements
        classTypes                = "/production/v2/getClassTypes",
        serviceTypes              = "/production/v2/getAppointmentTypes",
        stockList                 = "/production/v2/getStockList",
        //Get Availabilities
        appointmentAvailability   = "/production/v2/getAppointmentAvailabilities",
        classAvailability         = "/production/v2/getClassAvailabilities",
        getClasses                = "/production/v2/getClasses",
        getClassParticipants      = "/production/v2/getClassParticipants",
        //Add/Modify Bookings
        addBooking                = "/production/v2/addAppointmentBooking",
        addClassBooking           = "/production/v2/addClassBooking",
        cancelAppointment         = "/production/v2/cancelAppointment",
        rebookAppointment         = "/production/v2/rebookAppointment",
        //Patient Tools
        patients                  = "/production/v2/getPatients",
        searchPatients            = "/production/v2/searchPatients",
        addPatient                = "/production/v2/addPatient",
        addTreatmentNote          = "/production/v2/addTreatmentNote",
        invoices                  = "/production/v2/getInvoices",
        treatmentNotes            = "/production/v2/getTreatmentNotes",
        cases                     = "/production/v2/getCases",
        files                     = "/production/v2/getPatientFiles",
        fileUrl                   = "/production/v2/getFileUrl",
        //File uploading components (requires both calls to make the file go live)
        upload                    = "/production/v2/uploadFile",
        activateFile              = "/production/v2/setFileActive",
        //Logos
        locationLogo              = "/production/v2/getLocationLogo",
        practitionerPhoto         = "/production/v2/getPractitionerPhoto",
        //Large pull requests (site backup requests)
        appointments              = "/production/v2/getAppointments",
        allTreatmentNotes         = "/production/v2/getAllTreatmentNotes",
        allInvoices               = "/production/v2/getAllInvoices",
        //Custom Requests (requires special API on the Nookal interface side)
        practitionerTypes         = "/production/v2/getPractitionerAppointmentTypes",
        serviceMatrix             = "/production/v2/getServiceMatrix",
        //Invoices 
        addInvoice                = "/production/v2/addInvoice",
        addItemToInvoice          = "/production/v2/addItemToInvoice",
        addPaymentToInvoice       = "/production/v2/addPaymentToInvoice",
        deleteItemFromInvoice     = "/production/v2/deleteItemFromInvoice",
        deletePaymentFromInvoice  = "/production/v2/deletePaymentFromInvoice",
        deleteInvoice             = "/production/v2/deleteInvoice",
        getInvoice                = "/production/v2/getInvoice",
        getExtras                 = "/production/v2/getExtras",
        addExtra                  = "/production/v2/addPatientExtra"
		]
in
    Source

Invoke function:

let
    Source = InvokeApi("xxxxxx", ApiDictionary[verify], null)
in
    Source

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks again @v-shex-msft 

Sorry for all of the questions!

So when I input the 1st string of code It returns screenshot #1 (attached). I input the API key and I aasume the relative path is /production considering the root URL is "https://api.nookal.com" ???

It then takes me to screenshot #2 Does this need to be opened as HTML? From there not sure where to input the following code you provided?

Any Help Appreciated

Dan

[v-shex-msft]

HI @Anonymous ,

You can try to use Json.Document or Web.Page to replace Binary.From to receive the result.

Regards,

Xiaoxin Sheng

[PaulCooper]

You need a connector from the company or their API documentation to either handcrafted the URL (likely including that key), to use with the Power BI's inbuilt Web connector to extract the data, or if the API is more complicated write your own connector.