Forum Discussion

Holguinmora's avatar
Holguinmora
Helper I
6 years ago
Solved

Get Api Data from Web Api Connection

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
                }
            ]
        }
    ]
}

 

Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • JasonTX's avatar
    JasonTX
    6 years ago

    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!

3 Replies

  • JasonTX's avatar
    JasonTX
    Resolver I
    6 years ago

    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's avatar
      ArturFerreira
      New Member
      6 years ago

      Hello

       

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

      The procedure I am following is:

       

       

      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?