Forum Discussion
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!
Anyone who can help me?
2 Replies
- dvdbroekFrequent Visitor
Anyone who can help me?
- AnonymousNot applicable
Hi @ dvdbroek ,
This is related content, hope it can help you:
https://radacad.com/analyse-the-json-file-with-power-query
https://community.powerbi.com/t5/Desktop/Parsing-JSON-data-into-PowerBI-table/td-p/55946
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.