Forum Discussion
Trouble using a dynamic token function/variable for my data query
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"
Community ChampionHi 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,
Johannes
Hello 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"Yep this works! Awesome!
Together with some more changes that I made to the privacy levels, it seems like the dashboard can now finally refresh in the online workspace! I think I've spend around 15 hours now trying to get this dashboard running.
Thank you for helping me with these last steps Tex!