Forum Discussion
Trouble using a dynamic token function/variable for my data query
Hello!
For a dashboard I am batching data from an API. For acces I am using a token that's only valid for 2 hours.
For this reason I created a seperate query to retrieve this token. This Token query looks as follows:
I then use"Token" as a variable inside of my data query, and when refreshing the dashboard locally this works perfecty.
However on the PowerBi workspace online I get the following error:
[Unable to combine data] Section1/Data/Added Custom1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. The exception was raised by the IDataReader interface.
I tried solving this by editing privacy settings suggested online but this did not work.
As such I went to work on creating the Token function within the data query. This one looks as follows:
Sadly the same thing happens with this solution. It works perfectly locally, but now I am getting a different error on PowerBI online:
Information is needed in order to combine data;Information about a data source is required.. The exception was raised by the IDataReader interface.
Once again, privacy levels are set to none. What "Information about data source" is required?
Can someone point me in the right direction here? I feel like I'm getting close
Cheers!
Erik
It's slightly hard doing this blind, but how does it look if you try the code below?
let List = List.Generate( () => 0, each _ <= 80000, each _ + 2000 ), Source = Json.Document(Web.Contents("https://geolive-acc.bim.xxxx.nl/portal/sharing/rest/oauth2/token/?client_id=xxxxxx&client_secret=xxxxxxx&grant_type=client_credentials")), #"Converted to Table" = Table.FromRecords({#"Source"}), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"access_token", type text}, {"expires_in", Int64.Type}}), #"access_token1" = #"Changed Type"{0}[access_token], Token = #"Changed Type"{0}[access_token], #"Converted to Table1" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type2" = Table.TransformColumnTypes(#"Converted to Table1",{{"Column1", type text}}), #"Added Custom - QUERY" = Table.AddColumn(#"Changed Type2", "Custom.1", each Json.Document(Web.Contents("https://geolive-acc.bim.xxx.nl/server", [ RelativePath="rest/services/Hosted/xxxxxx/FeatureServer/0/query", Query = [ where="1=1", outFields="*", resultOffset=[Column1], token=Token, f="json" ] ] ))) in #"Added Custom - QUERY"
5 Replies
- tex628
Community Champion
Hi Anonymous,
I can't say for sure whats the issue here and in my mind the second solution should actually work. Try changing the function to instead just be normal query steps, and then reference "access_token1" as a string variable instead of calling the function.
Below is a somewhat similar query that works for me, i don't know if it can help in any way.let apiUrl = "API", body = [ client_id="ID", client_secret="SECRET", grant_type="client_credentials" ], Source = Json.Document(Web.Contents(apiUrl, [Headers = body])), response = Source[response], responseMessage = response[responseMessage], #"Converted to Table" = Record.ToTable(responseMessage), #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each [Name] = "access_token"), Token = #"Filtered Rows"{0}[Value], Content = Json.Document(Web.Contents("API", [ RelativePath = "?access_token=" & Token, Headers=[content_type="application/json"] ] )), #"Converted to Table1" = Record.ToTable(Content), #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table1", "Value", {"USER"}, {"USER"}), #"Expanded USER" = Table.ExpandListColumn(#"Expanded Value", "USER"),
Br,
JohannesHello tex628,
Thanks for your reply. I would like to try your suggestion, but I am not yet very familiar with manual coding within the advanced editor. I looked at your example but I can't quite figure it out.
Can you teach me how I need to adjust my current function to do what you described?
Is the fact that I have two sources in my query a problem?
I have included the first half of code of my current query below for reference.
let
token = () =>
let
Source = Json.Document(Web.Contents("https://geolive-acc.bim.xxxx.nl/portal/sharing/rest/oauth2/token/?client_id=xxxxxx&client_secret=xxxxxxx&grant_type=client_credentials")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"access_token", type text}, {"expires_in", Int64.Type}}),
access_token1 = #"Changed Type"{0}[access_token]
in
access_token1,
Source = List.Generate(
() => 0,
each _ <= 80000,
each _ + 2000
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Added Custom - QUERY" = Table.AddColumn(#"Changed Type", "Custom.1", each Json.Document(Web.Contents("https://geolive-acc.bim.xxx.nl/server",
[
RelativePath="rest/services/Hosted/xxxxxx/FeatureServer/0/query",
Query=[
where="1=1",
outFields="*",
resultOffset=[Column1],
token=token(),
f="json"
]
]
))),- tex628
It's slightly hard doing this blind, but how does it look if you try the code below?
let List = List.Generate( () => 0, each _ <= 80000, each _ + 2000 ), Source = Json.Document(Web.Contents("https://geolive-acc.bim.xxxx.nl/portal/sharing/rest/oauth2/token/?client_id=xxxxxx&client_secret=xxxxxxx&grant_type=client_credentials")), #"Converted to Table" = Table.FromRecords({#"Source"}), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"access_token", type text}, {"expires_in", Int64.Type}}), #"access_token1" = #"Changed Type"{0}[access_token], Token = #"Changed Type"{0}[access_token], #"Converted to Table1" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type2" = Table.TransformColumnTypes(#"Converted to Table1",{{"Column1", type text}}), #"Added Custom - QUERY" = Table.AddColumn(#"Changed Type2", "Custom.1", each Json.Document(Web.Contents("https://geolive-acc.bim.xxx.nl/server", [ RelativePath="rest/services/Hosted/xxxxxx/FeatureServer/0/query", Query = [ where="1=1", outFields="*", resultOffset=[Column1], token=Token, f="json" ] ] ))) in #"Added Custom - QUERY"