Forum Discussion

Alexandra_B's avatar
Alexandra_B
Icon for Helper III rankHelper III
4 years ago

Automatically refresh

Hello. Could someone help me please to automate the refresh of the data sources?   What I am doing now is to call a Postman collection in Microsoft Visual Studio Code (saving the results by execut...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
bcdobbs's avatar
bcdobbs
Icon for Community Champion rankCommunity Champion
4 years ago

We should be able to move the postman collection into an api call within power query. Are you able to share your existing vs code script? (Blank out any api keys)

Alexandra_B's avatar
Alexandra_B
Icon for Helper III rankHelper III
4 years ago

This is the code in .js :

const newman = require('newman'); // require newman in your project
const fs = require('fs');

// call newman.run to pass `options` object and wait for callback
newman.run({
    collection: require('./Name_of_the_collection.postman_collection'),
    reporters: 'cli'
}).on('request', (error, data) =>{
    if (error) {
        console.log(error);
        return;
    }

    const fileName = `response ${data.item.name}.json`;
    const content = data.response.stream.toString();

    fs.writeFile(fileName, content, function (error) {
        if (error) {
            console.error(error);
        }
    });
});

 

I have API key and authorization details on Postman when calling each API

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Do you have any documentation on the api you're using. Basically how it authenticates/paginates?

    • bcdobbs's avatar
      bcdobbs
      Icon for Community Champion rankCommunity Champion
      4 years ago

      The following might give you some idea.

      Based on combination of ideas from:

      How not to miss the last page when paging with Power BI and Power Query (thebiccountant.com)

      Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code Chris Webb's BI Blog (crossjoin.co.uk)

       

      If you don't need to deal with pagination:

      let
    BaseURL = "https://api.exampleurl.com/customer/v1",
    Path = "academic-years/2022/assessment/marks",
    APIKey = "Bearer PutYourKeyHere",
    Source = 
        Json.Document
            (Web.Contents(
                BaseURL, 
                    [
                        RelativePath = Path, 
                        Headers = [Authorization = APIKey]
                    ]
                )
            )
in
    Source

       

      If the API returns pages:

      let
    pBaseURL = "https://api.apiexample.com",
    pPath = "academic-years/2022/assessment/marks",
    pAPIKey = "Bearer PutYourKeyHere",
    Source = 
    List.Generate ( 
        () =>
        [
            Cursor = "FirstRecord",
            Result =          
                Json.Document
                    (Web.Contents(
                        pBaseURL, 
                            [
                                RelativePath = pPath, 
                                Headers = [Authorization = pAPIKey]
                            ]
                        )
                    )
        ],
        each [Cursor] <> null,
        each [
            Cursor = Number.ToText([Result][cursor]),
            Result = 
                Json.Document
                    (Web.Contents(
                        pBaseURL, 
                            [
                                RelativePath = pPath, 
                                Query = [ cursor = [Cursor] ],
                                Headers = [ Authorization = pAPIKey ]
                            ]
                        )
                    )
        ]

    )
in
    Source

       

      The above needs tweaking depending on how the paging and authentication works but hope it gives you some ideas.

       

      • Alexandra_B's avatar
        Alexandra_B
        Icon for Helper III rankHelper III
        4 years ago

        Oh dear, I`m even more confused.
        To access the API, I have the link , email address to login and MD5 of the password.
        Now, I have 174 calls on the  Postman collection. I don`t know how to connect the collection with Power BI 😞 I have created a connector in Power Apps, Dataverse using the collection but when I try to  get data into Power Bi, I cannot see the connector.