Refresh API sources in PBI service to Generate Access Toekn dynamically and iterate all pages

[venusura483]

Hi ,

I'm trying to (1) generate API access token dynamically and (2) iterate all pages with relativepath, My below power Query is working fine in Desktop but not refrehsing in Power BI service with the below error: 

Attached below error and code.

Error:

Data source error: Information is needed in order to combine data;Information about a data source is required.. 

The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. 

CODE:

//GetAccessToekn:

() =>

let

    url = "https://testapi.apples.com/api/v1/auth",

    headers = [#"Authorization"="Bearer <<access_token>>"],

    postBody = [

        grant_type = "refresh_token", 

        refresh_token = "<<refresh_token>>"

        ],

    response = Json.Document(Web.Contents(url,

        [

            Headers = headers,

            Content = Text.ToBinary(Uri.BuildQueryString(postBody))

        ])),

    access_token = response[access_token]

in

    access_token

//Iterate all pages with hardcoded page numbers(0..15)

let

    Source = {0..15},

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents("https://testapi.apples.com/api/v1",

        [ RelativePath="users",

           Query=[page=[Column1]],

                       Headers=[#"Authorization"="bearer " & GetAccessToken(), 

                                 #"accept" = "text/plain", 

                                 #"Content-Type"="application/json"], 

                        ManualStatusHandling = {404, 400}]))),

    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Items", "Pagination"}, {"Items", "Pagination"}),

    #"Expanded Items" = Table.ExpandListColumn(#"Expanded Custom", "Items"),

    #"Expanded Items1" = Table.ExpandRecordColumn(#"Expanded Items", "Items", {"Id", "Username", "FirstName", "Surname", "Email", "IsActive", "SkillIds", "PositionIds", "PrimaryLocation"}, {"Id", "Username", "FirstName", "Surname", "Email", "IsActive", "SkillIds", "PositionIds", "PrimaryLocation"}),

    #"Expanded SkillIds" = Table.ExpandListColumn(#"Expanded Items1", "SkillIds"),

    #"Expanded PositionIds" = Table.ExpandListColumn(#"Expanded SkillIds", "PositionIds")

in

    #"Expanded PositionIds"

Any help would be much appriciated.

Many Thanks

[aj1973]

Hi @venusura483 

You will need an app registration to get it work in the service. 

Here is a video to explain it

https://www.youtube.com/watch?v=2RZkc_qrV1g&t=610s

[venusura483]

@aj1973 , The suggested video shows refresh token for Power BI rest API. I'm looking to extract data from web API, I can connect and extract data but not able to refresh the same in Power BI service.