Web API call works in desktop, but when published Im unable to refresh

[evidence]

Hi,

I have a report that works fine in Power BI desktop (retrieving JSON data from a public web API using Json.Document(Web.Contents()) ). However, when I publish I get the error that "Your data source can't be refreshed because the credentials are invalid". Full query below. URLs changed for privacy:

let

    AuthKey = GetSessionId(),
    url = "https://publicapi.com",
    body = "{typeName: 'Project',
        fields: ['name', 'TrackStatus.name', 'BudgetStatus.name'],
        where: {
	    _type: 'Compare',
	    leftExpression: {fieldName: 'State'},
	    operator: 'Equal',
	    rightExpression: {value: 'Active'}
	},
    }",

    Source = Json.Document(Web.Contents(url,[          
         Headers = [#"Authorization"= AuthKey ,
                    #"Content-Type"="application/json"],
         Content = Text.ToBinary(body) 
         ]   
    )),
    entities = Source[entities],
   in
    entities

The GetSessionId() method refers to another query to another web api to receive the session id

let
    
    Source = Json.Document(Web.Contents("https://publicapi.com",[            
         Headers = [#"Content-Type"="application/json"],
         Content = Text.ToBinary("{userName:'<user>',password:'<pass>'}") 
         ]   
    )),
    sessionId = "Session " & Source[sessionId]
in
    sessionId

[v-shex-msft]

Hi @evidence,

Currently, power query custom function only available on desktop side. Perhaps you can merge your functions to one query and try again.

Regards,

Xiaoxin Sheng

[evidence]

Hi,

Thanks for your answer. Any idea how I would merge the two queries?

I tried the following, but had the same error on the server.

let

    login = Json.Document(Web.Contents("https://api.com/login",[Headers = [#"Content-Type"="application/json"],Content = Text.ToBinary("{userName:'<user>',password:'<pass>'}")])),
    AuthKey = "Session " & login[sessionId],
    url = "https://api.com/EntityQuery",
    body = "{typeName: 'Project',
        fields: ['name', 'TrackStatus.name', 'BudgetStatus.name'],
        where: {
	    _type: 'Compare',
	    leftExpression: {fieldName: 'State'},
	    operator: 'Equal',
	    rightExpression: {value: 'Active'}
	},
    }",

    Source = Json.Document(Web.Contents(url,[          
         Headers = [#"Authorization"= AuthKey ,
                    #"Content-Type"="application/json"],
         Content = Text.ToBinary(body) 
         ]   
    )),
    entities = Source[entities],
    #"Converted to Table" = Table.FromList(entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "TrackStatus", "BudgetStatus"}, {"Column1.id", "Column1.name", "Column1.TrackStatus", "Column1.BudgetStatus"}),
    #"Expanded Column1.TrackStatus" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.TrackStatus", {"name"}, {"Column1.TrackStatus.name"}),
    #"Expanded Column1.BudgetStatus" = Table.ExpandRecordColumn(#"Expanded Column1.TrackStatus", "Column1.BudgetStatus", {"name"}, {"Column1.BudgetStatus.name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.BudgetStatus",{{"Column1.BudgetStatus.name", "Budget Status"}, {"Column1.name", "Project Name"}, {"Column1.TrackStatus.name", "Schedule Status"}, {"Column1.id", "Project Id"}})
in
    #"Renamed Columns"