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

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