Prompting for values in invoked function,

[richardc1]

I need to run a function that returns a token, without the credentials being stored in the function.

I have developed a function that prompts for credentials when run, and outputs the token, which works fine.

GetToken

let
Source = (Client_ID as text, Client_Secret as text, un as text, pw as text, Token as text) =>
let
body = [grant_type="password", client_id=Client_ID, client_secret=Client_Secret, username=un, password=pw, Authorization=Token],
Data=Json.Document(Web.Contents("https://instance.service-now.com/oauth_token.do",[Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(Uri.BuildQueryString(body))])),
access_token = Data[access_token]
in
access_token
in
Source

This works, and returns a token when the correct credentials are parsed.

However, I want to embed this function in another query, such that this query prompts for the credentails, returning the token which can then be used to authenticate to an API URL.

e.g.

let
Source = Json.Document(Web.Contents("https://instnace.service-now.com/api/now/table/incident?sysparm_limit=100", [Headers=[Authorization="Bearer "&GetToken]])),
#"Converted to Table" = Table.FromRecords({Source}),

...etc

However, I get an error immediately:

"We cannot apply operator & to types Text and Function."

I need to prompt for credentials, invoke the GetToken function, assign the output of the GetToken function to authenticate via an API call.

I have tried Token = GetToken don't work (same error)

This is just me not getting M at all, I hope this is relatively easy/ 

[richardc1 · Accepted solution]

I have found another solution:

Solved: Re: Avoiding free text credentials in Power BI que... - Microsoft Fabric Community

Calling the creds from a slsx in a secure location, rather than entering manually.

[MFelix]

Hi @richardc1 ,

This is related with the fact that you are concatenating the "Bearer " with your GetToken

Try the following change:

let
Source = Json.Document(Web.Contents("https://instnace.service-now.com/api/now/table/incident?sysparm_limit=100", [Headers=[Authorization="Bearer "& Text.From ( GetToken) ]])),
#"Converted to Table" = Table.FromRecords({Source}),

...etc

Has you can see I have wrap the GetToken with a Text.From this will convert the result to a text string, even if it's a text string sometimes you need to do this workaround

[richardc1]

Thanks so much for your reply.

I tried this and got error:

Embedding the GetToken query this way does not prompt for credentials, it tries to extract text from that function directly and fails.

"Expression.Error: We cannot convert a value of type Function to type Text."

I need to call for parameters first, invoke GetToken with those credentials, store the output (this bit is key, and one I don't understand), and use that output (i.e. the bearer token) in the Json call.

e.g
let
source = (id as text, secret as text, un as text, pw as text, _token as text)=>
Token = text.from(GetToken(id,secret,un,ow,_token)

in

But the syntax is wrong, gices error: "Token literal expected" 

I think we are getting close!