Forum Discussion

dvdbroek's avatar
dvdbroek
Frequent Visitor
5 years ago

Extract specific value from JSON file and put it in 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!

Need Help

2 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi dvdbroek 

    In your code you'll have a step that calls that function and results in the single row table, something like this

     

    GetAPIKey = Call_API_Key_Function(.......)

     

    giving a table like this

     

    To get the Key use a line like this, where GetAPIKey is the name of the step that calls the function and results n  the table - change this as appropriate to suit your code.

     

    KeyFromAPI = GetAPIKey[Key]{0}

     

     

    Regards

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.