Automatically refresh

[Alexandra_B]

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 executing a .js file).

Everytime I execute the file, I get my data source refreshed, and I know I can set up a Schedule Refresh in the portal for each data set.

The problem is that I can only execute the file while at work`s computer, because there is everything installed.

If I go on holiday or work from home, I am not able to refresh the data.

What are you using to get the data source updated and will someone help me to do it please?

I don`t know how to use Azure to create a  SQL database and populate it and I cannot get the data through Web option on Microsoft Power BI Desktop.

Please help 😞

Thank you

[bcdobbs]

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]

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]

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

[bcdobbs]

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

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]

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]

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.

[Alexandra_B]

I get a 403 (forbidden) error while trying to test the connector. It looks like I`m not having any luck with this...
Thank you for trying to help.

[bcdobbs]

Sorry haven't been more succesful. If you have some documentation from the API I'd be more than happy to try and customise some power query for you.