Forum Discussion

Alexandra_B's avatar
Alexandra_B
Helper 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
Community 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
    Helper 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
      Community Champion
      4 years ago

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

      • bcdobbs's avatar
        bcdobbs
        Community 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.