Extract specific value from JSON file and put it in a variable

[dvdbroek]

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!

[PhilipTreacy]

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.

[v-alq-msft]

Hi, @dvdbroek 

Based on your description, I assume that the step 'Source' returns a record. I created data to reproduce your scenario. 

You may add the following step to get the value of 'Key'.

= Record.Field(Source,"Key")

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.