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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
trwatts
Helper I
Helper I

Issue with returning a table.

Hi Everyone 

New to M query and having issues get a table of records. 

I am calling through API.

Bearer Token needs to be called.

The URL has a dynamic ID at the end of the url which returns a set of values. 

When i run the query with a static centre id at the end of the URL i have no issues at all.

I need to create a query that dynamically runs through the availble ids to pull all of the data. 

 

I have come to a few different issues. 
Line 14. From my understanding this will try to match the key of "Centerid" to the "source([Centreid]). The two table do not have a common centreid field in the source the key id room_id which is an unrealted number.

When i run it I get the below.

trwatts_1-1635386718717.png

 


I then tried replacing "Centerid" with "ids" that has a coressponding number to each row in the source table. 

Once run i get the following error. 

trwatts_0-1635386635927.png

I have tried countless other things to try get the result but am now hitting my head up against a wall. 

 Completely Stuck. 
Any help would be fantastic. 

 

1 let
2 Centreid = Centreids,
3 ids = List.Generate(() => 100000, each _ > 0, each _ -1),
4 url = "https://auth.XXXXXXXX.com/api/v1/auth",
5 body = "{ ""user"": ""XXXXXXXX"",""password"": ""XXXXXXX""}",
6 tokenResponse = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] )),
7 data1 = tokenResponse[data],
8 token = "Bearer " & data1[token],
9 source = (Centreid as text) => Json.Document(Web.Contents("https://office.XXXXXXXXX.com/api/enterprise/booking/center/rooms/summary",

10 [Query= [postid=Centreid],
11 Headers=[#"x-api-key"="XXXXXXXXXXXXXXXXXXXXXXXXXXX",
12 Authorization=token,
13 #"Content-Type"="application/json"]])),
14 Output= Table.AddColumn(Centreid,"Output", each source([Centreid]))
15 in
16 Output

 

 

 

 

 

1 ACCEPTED SOLUTION

Have you seen this post?

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

 

This and a few other posts of Chris' on the topic may offer some guidance.

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

14 REPLIES 14
trwatts
Helper I
Helper I

@KNP 

Is the below what you mean by code block? 

The Centreids references a separate list. 

 

 let
    Centreid = Centreids,
    ids = List.Generate(() => 100000, each _ > 0, each _ -1),
    url = "https://auth.XXXXXXXX.com/api/v1/auth",   
    body  = "{ ""user"": ""PCYCNSW-API"",  ""password"": ""XXXXXXX""}",
    tokenResponse = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] )),
    data1 = tokenResponse[data],
   token = "Bearer " & data1[token],
    source = (Centreid as text) => Json.Document(Web.Contents("https://office.XXXXXXXXX.com/api/enterprise/booking/center/rooms/summary", [Query=10	[postid=Centreid],
    Headers=[#"x-api-key"="XXXXXXXXXXXXXXXXXXXXXXXXXXX", 
    Authorization=token,  
    #"Content-Type"="application/json"]])),
    Output= Table.AddColumn(Centreid,"Output", each source([Centreid]))
 in
   Output

 

Yes, that's what I meant by code block, thanks, that's much easier to read.

When I copied that code, Power Query complained about the syntax around the [postid=Centreid] section, so I had to mess around with that a bit.

 

Based on what I understand, I'm wondering if you'd be better off removing the last line so you have a working function that you could then use the 'Add Column' >> 'Invoke Custom Function' option.

 

So you end up with this...

let
  Centreid = Centreids,
  ids = List.Generate(() => 100000, each _ > 0, each _ - 1),
  url = "https://auth.XXXXXXXX.com/api/v1/auth",
  body = "{ ""user"": ""PCYCNSW-API"",  ""password"": ""XXXXXXX""}",
  tokenResponse = Json.Document(
    Web.Contents(
      url,
      [Headers = [#"Content-Type" = "application/json"], Content = Text.ToBinary(body)]
    )
  ),
  data1 = tokenResponse[data],
  token = "Bearer " & data1[token],
  source = (Centreid as text) =>
    Json.Document(
      Web.Contents(
        "https://office.XXXXXXXXX.com/api/enterprise/booking/center/rooms/summary",
        [
          Query = 10,
          postid = Centreid,
          Headers = [
            #"x-api-key"    = "XXXXXXXXXXXXXXXXXXXXXXXXXXX",
            Authorization   = token,
            #"Content-Type" = "application/json"
          ]
        ]
      )
    )
in
  source

 

The gif below, Query1 is your code that I pasted above, then add column and invoke the function based on the id column. 

Mine errors obviously because the lack of valid url etc. but hopefully this will get you a little closer.

 

8wIJ6xIfTw.gif

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

I had a type in my code so updated. Was just the line which had "Query=10"
Followed the gif and got this

 

Notepad copy of the error. 
I think its closer

where the end of the url reads rooms/summary?postid=11169 it should read
rooms/summary/11169

And i really appreciate you helping me to trouble shoot this. 

 

let
  Centreid = Centreids,
  ids = List.Generate(() => 100000, each _ > 0, each _ - 1),
  url = "https://auth.xxxxxxxxxxx.com/api/v1/auth",
  body = "{ ""user"": ""xxxxxx"",  ""password"": ""xxxxxx""}",
  tokenResponse = Json.Document(
    Web.Contents(
      url,
      [Headers = [#"Content-Type" = "application/json"], Content = Text.ToBinary(body)]
    )
  ),
  data1 = tokenResponse[data],
  token = "Bearer " & data1[token],
  source = (Centreid as text) =>
    Json.Document(
      Web.Contents(
        "https://office.xxxxxxxx.com/api/enterprise/booking/center/rooms/summary",
        [
          Query =[
          postid = Centreid],
          Headers = [
            #"x-api-key"    = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
            Authorization   = token,
            #"Content-Type" = "application/json"
          ]
        ]
      )
    )
in
  source

 

 

Hi,

 

Can you please delete the code in yor post. It includes passwords which I accidentally included.

I had a type in my code so updated. Was just the line which had "Query=10"
Followed the gif and got this

trwatts_1-1635393869608.png

 

Notepad copy of the error. 
I think its closer

where the end of the url reads rooms/summary?postid=11169 it should read
rooms/summary/11169

And i really appreciate you helping me to trouble shoot this. 

 

 

 

This is probably where my knowledge of accessing APIs via Power Query ends but I would've thought that something like the below would be more likely to return the format that you're expecting. (note the 'RelativePath...')

 

// Query2
let
  url = "https://auth.myxplor.com/api/v1/auth",
  body = "{ ""user"": ""xxxxxxxxx"",  ""password"": ""xxxxxxxxx""}",
  tokenResponse = Json.Document(
    Web.Contents(
      url,
      [Headers = [#"Content-Type" = "application/json"], Content = Text.ToBinary(body)]
    )
  ),
  data1 = tokenResponse[data],
  token = "Bearer " & data1[token],
  source = (Centreid as text) =>
    Json.Document(
      Web.Contents(
        "https://office.myxplor.com/api/enterprise/booking/center/rooms/summary",
        [
          RelativePath=Centreid,
          Headers = [
            #"x-api-key"    = "xxxxxxxxxxxxxxxxxxxxxxxxxxxx",
            Authorization   = token,
            #"Content-Type" = "application/json"
          ]
        ]
      )
    )
in
  source

 

 

Doing this with the code you provided returns an error...

KNP_0-1635403131594.png

Unless of course that is expected because you haven't provided actual creds to access the API, which is kind of what I'd expect you'd do. 🤔

 

Edit: removed sensitive info.

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Hi KNP.

Its works in desktop however when published to the web and I try a refresh i get this error. 

[Unable to combine data] Section1/Centreids/Expanded Query1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Hi @trwatts 

 

I found a similar question, see if the solution there works. Unable to combine data...AutoRemovedColumns1...Please rebuild this data combination (microsoft.com)

 

Best Regards,
Community Support Team _ Jing

Have you seen this post?

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

 

This and a few other posts of Chris' on the topic may offer some guidance.

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Thank you KNP that has worked in the desktop version. You have saved me thank you. 
Will need to test once published as I have found some weird errors but will come back as soon as I have tested.
Would you mind deleted the code from your last post I inadvertantly posted passwords etc. 
They have been changed but would still like that info gone. 

Again thank you I have been trying for weeks to get this far.
@KNP 

Have edited the post to exclude the sensitive info.

Not sure how to deal with the @Syndicate_Admin post though as I don't know if anyone monitors that.

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
KNP
Super User
Super User

Shouldn't the line...

 Output= Table.AddColumn(Centreid,"Output", each source([Centreid]))

Say...

 Output= Table.AddColumn(source,"Output", each source([Centreid]))

 

As in, your previous step.

I may be missing something.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Just tried it and unlike the other times i was thinking abot it for a while then produced this error. 

 

trwatts_0-1635388449884.png

 

Sorry, it's really hard to read the code you posted.

If you could post your complete code, inside a code block with some more detail about other queries etc. it may be easier to help. 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.