Get Api Data from Web Api Connection

[Holguinmora]

Hi to all

I´m new on Power Bi, I just made a connection to a Web API but I have not figure out what to do to get the data.

The connection is telling me that the Api has Total Count 117 lines and the LastTimeStamp is 4702396173 (Metadata fields) according to the APi description from the next table:

What should be the next steps to be able to get the data from ClienId, TimeStamp, Code, ect...?

{
    "MetaCollectionResult":
    {
        "TotalCount":0,
        "LastTimeStamp":0
    },
    "Clients":
    [
        {
            "ClientID":0,
            "TimeStamp":0,
            "Code":null,
            "Name":null,
            "Active":false,
            "Tag":null,
            "Territory":null,
            "RepresentativeCode":null,
            "RepresentativeName":null,
            "StreetAddress":null,
            "ZIP":null,
            "ZIPExt":null,
            "City":null,
            "State":null,
            "Country":null,
            "Email":null,
            "Phone":null,
            "Mobile":null,
            "Website":null,
            "ContactName":null,
            "ContactTitle":null,
            "Note":null,
            "Status":null,
            "PriceLists":
            [
            ],
            "CustomFields":
            [
                {
                    "Field":null,
                    "Value":null
                },
                {
                    "Field":null,
                    "Value":null
                }
            ]
        },
        {
            "ClientID":0,
            "TimeStamp":0,
            "Code":null,
            "Name":null,
            "Active":false,
            "Tag":null,
            "Territory":null,
            "RepresentativeCode":null,
            "RepresentativeName":null,
            "StreetAddress":null,
            "ZIP":null,
            "ZIPExt":null,
            "City":null,
            "State":null,
            "Country":null,
            "Email":null,
            "Phone":null,
            "Mobile":null,
            "Website":null,
            "ContactName":null,
            "ContactTitle":null,
            "Note":null,
            "Status":null,
            "PriceLists":
            [
            ],
            "CustomFields":
            [
                {
                    "Field":null,
                    "Value":null
                },
                {
                    "Field":null,
                    "Value":null
                }
            ]
        }
    ]
}

[JasonTX · Accepted solution]

Go to Power Query and select the correct query. You should see something like this:

 

Click List. 

Then click 'To Table' on the top left. Click 'OK' on the prompted window. You should see this as below

Click this button circled above. Uncheck the option in the 'Use original column name as prefix' then click OK. You will see several more columns that have this option as well once it is expanded. That is because there is a nested list. Do the same for those columns and make sure to click 'Expand to New Rows'.

Hope this helps!

[ArturFerreira]

Hello

I’m trying to get datas from a web service, but without success.

The procedure I am following is:

• Open PowerBI
• Select “get data”
• Other
• Web
• Selected “basic”
• I have informed the https://api.vhsys.com/v2/extratos/:id
• Then pressed ENTER), but it showed the querry has 404 error

The API documentation shows the following PHP

<?php

    $curl = curl_init();

    curl_setopt_array($curl, array(

        CURLOPT_URL => "https://{api_address}/v2/extratos/:id",

        CURLOPT_RETURNTRANSFER => true,

        CURLOPT_ENCODING => "",

        CURLOPT_MAXREDIRS => 10,

        CURLOPT_TIMEOUT => 30,

        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,

        CURLOPT_CUSTOMREQUEST => "GET",

        CURLOPT_HTTPHEADER => array(

            "Access-Token: SEU (Access-Token) DE ACESSO ",

            "Secret-Access-Token: SEU (Secret-Access-Token) DE ACESSO",

            "Content-Type: application/json"

        ),

    ));

    $response = curl_exec($curl);

    $erro = curl_error($curl);

?>

Does anyone can help me?

[Holguinmora]

It worked perfectly!!!!!

Thank you very much!!!!!