Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ErikBI
Resolver I
Resolver I

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:

Token.png

 

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:

token2.png

 

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 

1 ACCEPTED SOLUTION
tex628
Community Champion
Community Champion

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"

Connect on LinkedIn

View solution in original post

5 REPLIES 5
tex628
Community Champion
Community Champion

Hi @ErikBI

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


Connect on LinkedIn

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
Community Champion
Community Champion

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"

Connect on LinkedIn

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!

tex628
Community Champion
Community Champion

No worries, happy to help! 🙂 


Connect on LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.