Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, Wer have pulled data from a restful api and that give us the session information, we are then wanting to use that information to pull more infromation out.
My question is how would you reference and array within M and have two 'LETS'... see below for code.
let apiUrl = "https://api.secondstreetapp.com/sessions", data ="{'sessions':[{'username':'**', 'password': '**'}]}", options = [ Headers =[#"Content-Type"="application/json", #"X-Api-Key"="*****"], Content = Text.ToBinary(data) ], Value = Web.Contents(apiUrl,options), #"Imported JSON" = Json.Document(Value,65001), sessions = #"Imported JSON"[sessions], sessions1 = sessions{0} in sessions1 let apiUrl = "https://api.secondstreetapp.com/message_campaigns?statusTypeId=1", options = [ Headers =[#"Content-Type"="application/json", #"X-Api-Key"="*****", #"Authorization"=sessions1.access_token, #"X-Organization-Id"=sessions1.organizationid] ], Value = Web.Contents(apiUrl,options) in Value
So im also wanting to know how to access the 'access_token' and 'organizationid' in the sessions1 array.
Thanks
If you want to have it all in one query, you have to nest the first into the second to reference to it:
let FirstStep = let apiUrl = "https://api.secondstreetapp.com/sessions", data ="{'sessions':[{'username':'**', 'password': '**'}]}", options = [ Headers =[#"Content-Type"="application/json", #"X-Api-Key"="*****"], Content = Text.ToBinary(data) ], Value = Web.Contents(apiUrl,options), #"Imported JSON" = Json.Document(Value,65001), sessions = #"Imported JSON"[sessions], sessions1 = sessions{0} in sessions1 , // don’t forget this comma apiUrl = "https://api.secondstreetapp.com/message_campaigns?statusTypeId=1", options = [ Headers =[#"Content-Type"="application/json", #"X-Api-Key"="*****", #"Authorization"=sessions1.access_token, #"X-Organization-Id"=sessions1.organizationid] ], Access_token = FirstStep[Access_token], // you can reference this now by the name of this step OrganizationalId = FirstStep[OrganizationalId], // you can reference this now by the name of this step Value = Web.Contents(apiUrl,options) in Value
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks for the reply Imke,
I tried the code you suggested and I get the error message
Expression.Error: The name 'sessions1.access_token' wasn't recognized. Make sure it's spelled correctly.
The first step is retreiving the access_token ok, so it seems to be the referencing that its falling over.
The subsequent errors are
Expression.Error: The field 'Access_token' of the record wasn't found. Details: access_token=**** embedded_errors=List id=**** is_new_platform_joyride_complete=TRUE organization_id=**** organization_user_guid=***** organization_user_id=***** organization_users=Record
Many Thanks
C
Using @ImkeF script. Make sure to refrence session1.access_token as FirstStep[access_token] or whatever the structure. For example:
let FirstStep = let apiUrl = "https://api.secondstreetapp.com/sessions", data ="{'sessions':[{'username':'**', 'password': '**'}]}", options = [ Headers =[#"Content-Type"="application/json", #"X-Api-Key"="*****"], Content = Text.ToBinary(data) ], Value = Web.Contents(apiUrl,options), #"Imported JSON" = Json.Document(Value,65001), sessions = #"Imported JSON"[sessions], sessions1 = sessions{0} in sessions1 , // don’t forget this comma apiUrl = "https://api.secondstreetapp.com/message_campaigns?statusTypeId=1", options = [ Headers =[#"Content-Type"="application/json", #"X-Api-Key"="*****", #"Authorization"=FirstStep[access_token], #"X-Organization-Id"=sessions1.organizationid] ], Value = Web.Contents(apiUrl,options) in Value
Thanks Hugo, the error i am receiving now is
Expression.Error: The 'Content-Type' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, If-Modified-Since, Prefer, Referer
which makes me think it is connecting ok and is the query now thats at fault
Is it becasue it is connecting in the same instance as a authorized connection?
That's weird!
If you use anonymous connection on first call it should use aonymous call on the second call as well. So try to make sure that on both instances you use anonymous connection when prompted. This will allow you to use the Conten-Type
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.