Forum Discussion

dvdbroek's avatar
dvdbroek
Frequent Visitor
5 years ago
Solved

Getting a specific value from a table that comes from a JSON file into a variable

Dear M query king and queens,

I made a custom function that retrieves an API Acces key so i can retrieve data from an API. The code is as following:

 

 

 

(UserName as text, OfficeCode as text, Password as text)=>

let
   url = "https://api.basecone.com/v1/Authentication/ApiAccessKeys",

   body = "{""clientIdentifier"":""xxxxxxxxxxx"",
        ""clientSecret"":"xxxxxxxxxxxxxxx"",
        ""username"":" & Text.FromBinary(Json.FromValue(UserName)) & "," & "
        ""password"":" & Text.FromBinary(Json.FromValue(Password)) & "," & "
        ""officeCode"":" & Text.FromBinary(Json.FromValue(OfficeCode)) & "}",

    #"Identifier" = "xxxxxxxxxxxxxxx"
 
    Source = Json.Document(Web.Contents(url,[

           Headers = [#"Content-Type"="application/json"],

           Content = Text.ToBinary(body)] )),


           
  #"Converted to Table" = Record.ToTable(Source),

   #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Value", type text}}),
  #"Base64Key" = Table.AddColumn(#"Converted to Table", "APIKeyBase64", each Binary.ToText(Text.ToBinary(Identifier & [Value]), 0)),
  FinalOutput = Table.First(#"Base64Key")

 
in
FinalOutput

 

 

 

This code gives me a table with 3 colums and 1 row because i only retrieved the first row of the table. The headers are:

"name"       "Value"    "Key"

 

Now i want to put the value of the colum "Key" into a variable, the rest of the table i dont need. But i cant seem to get it working within the advanced Editor. The file i get from the API is JSON , so maybe i can directly put the value into a variable from the JSON response?

 

Basicly im looking for something:

 

#"KeyFromApi" = Column "Key".value     (i know the syntax doenst make sense, but its the best way i can explain)

 

Much thnx in advance!

 

 

 

 

 

2 Replies