Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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.
Proud to be a Super User!
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.