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
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.

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

        You should be able to use a connector like that in power automate and save response to one drive or sharepoint. From there you just read the file in power bi.