Automatically refresh

Question

Hello.Could someone help me please to automate the refresh of the data sources?&nbsp;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.&nbsp;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&nbsp; SQL database and populate it and I cannot get the data through Web option on Microsoft Power BI Desktop.&nbsp;Please help 😞&nbsp;Thank you&nbsp;&nbsp;&nbsp;

bcdobbs · Answer

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.

bcdobbs · Answer

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 · Answer

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) =&gt;{
    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);
        }
    });
});&nbsp;I have API key and authorization details on Postman when calling each API

bcdobbs · Answer

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

bcdobbs · Answer

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)
&nbsp;
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
&nbsp;
If the API returns pages:
let
    pBaseURL = "https://api.apiexample.com",
    pPath = "academic-years/2022/assessment/marks",
    pAPIKey = "Bearer PutYourKeyHere",
    Source = 
    List.Generate ( 
        () =&gt;
        [
            Cursor = "FirstRecord",
            Result =          
                Json.Document
                    (Web.Contents(
                        pBaseURL, 
                            [
                                RelativePath = pPath, 
                                Headers = [Authorization = pAPIKey]
                            ]
                        )
                    )
        ],
        each [Cursor] &lt;&gt; null,
        each [
            Cursor = Number.ToText([Result][cursor]),
            Result = 
                Json.Document
                    (Web.Contents(
                        pBaseURL, 
                            [
                                RelativePath = pPath, 
                                Query = [ cursor = [Cursor] ],
                                Headers = [ Authorization = pAPIKey ]
                            ]
                        )
                    )
        ]

)
in
    Source
&nbsp;
The above needs tweaking depending on how the paging and authentication works but hope it gives you some ideas.
&nbsp;

Forum Discussion

Automatically refresh

8 Replies

Related content

Automatics Refresh

Automatic refresh error

Automatic data refresh limition

Fabric Lakehouse SQL Endpoint Automatic Refresh

Automatically refresh visual