Use retrieved values within M Query

[Anonymous]

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

[ImkeF]

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

[Anonymous]

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

[hugoberry]

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

[Anonymous]

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?

[hugoberry]

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