Unable to combine data references - may not directly access a data source

[sfog]

Hi

When I try to refresh my query in the Power BI service, I get this error. It works on the PBI desktop.

I have seen numerous posts and believe I worked through all - but none were specific to my problem.

I applied the approach of splitting the queries as per the "Excel Guru's guide (by Ken Puls).

I also applied the 'relativepath' concept as per some other well documented blogs.

This works well for all other Webservice calls we make - except in this case below.

Can anyone suggest how I could change the query below to avoid the refresh error in the PBI Service. I believe I need to separate the external datasource call (URL-bit) from the query that returns the token - but not sure how to do that if the external call needs the token?

**Note: ‘GetAccessToken’ returns an oauth authorisation bearer token. This cannot be hard coded as it expires every 15 minutes.

let

   AccessTokenHeader = GetAccessToken,                                                            

   GetJsonQuery = Web.Contents("https://xxxxxxxxxxxxxx.cloud/",

     [

        RelativePath="api/2.0/alarms?page=0&size=1000&sort=timestamp_occured,desc",

        Headers = [Authorization=AccessTokenHeader]

     ]

)

in

    #"GetJsonQuery"

**Note:

I can solve the error if I remove the query "AccessTokenHeader = GetAccessToken" and hard code the OAUTH access token - but the problem is, the token expires every 15 minutes so I need to generate a new token whenever I refresh the data.

[sfog · Accepted solution]

Hi

This is solved.

When I bring the code back into the query, as opposed to calling another query to generate the token, the problem goes away.

Regards 

[Anonymous]

Hi @sfog  , I'm having the same issue. Please can you help me and show me how you organized your code?
I use this 2 queries but I don't know how to combine them in 1.

Token:
let
ContentValue = "username="&User&"&password="&Password&"&grant_type=password&scope="&Scope,
GetJson = Web.Contents(#"ADFS Endpoint",
[
Headers = [#"Accept"="application/json",
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary(ContentValue),
RelativePath= #"ADFS RelativePath"
]
),
FormatAsJson = Json.Document(GetJson),
#"Converted to Table" = Record.ToTable(FormatAsJson),
access_token = #"Converted to Table"{0}[Value]
in
    access_token

Data:

let
Source = Web.Contents(#"Jira Environment" , [Headers=[#"Content-Type"="application/json", Authorization="Bearer "& Token, Referer= User], RelativePath = #"API" & "?links=overview&rows=1000&filter="& #"Filter"]),
Result = Json.Document(Source),
    foundIssues = Result[foundIssues],
    issues = foundIssues[issues],
    #"Converted to Table" = Record.ToTable(issues)
in
  #"Converted to Table"

[v-xuding-msft]

Hi @sfog ,

Glad you have solved the problem. You could accept your answer as solution so that we can close the thread.  More people will benifit from here.  

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.