SOLVED

How to use the result of one query in another query?

Copper Contributor

I'm trying to call an API, where I need (for starters) 2 queries (M code). For this, the 2nd query calls an outcome from the 1st query.

 

However, I get the error message in doing so:

"[Expression.Error] We cannot apply operators & to types Text and Table".

 

When I execute the queries in Power BI, this does not cause any problems. However, it is necessary for an assignment to do this in Excel.

 

How can I call the result from the 1st query (Access_token) in the 2nd query?

 

The first query, Access_token:

 

let



GetJson=Web.Contents(Link_getToken,

[

   Headers=[#"Content-Type"="application/json"],

   Content=Text.ToBinary(

      "{ ""_username"": """&Username&""", ""_password"": """&Password&"""}"

   )

]

),



   FormatAsJson = Json.Document(GetJson),

   #"Converted to Table" = Record.ToTable(FormatAsJson),

   access_token_result = #"Converted to Table"{0}[Value]

in

    access_token_result

 

 

Second table (the problem is: "...Authorization=" XXX "& Access_token..."):

 

 

let



GetJson_labels=Web.Contents(Link_getLabel,

[

   Headers=[#"Content-Type"="application/json",

   Authorization=" XXX "& Access_token

   ]

]

),

   FormatAsJson_labels = Json.Document(GetJson_labels),

... etc.

in

    #"Renamed Columns"

 

 

7 Replies
Make sure the token query returns a single value, not a table by clicking on the value of the token in the last step of that query
Hi, when applying the M code in the query editor, the applied steps are as follows:
https://ibb.co/hgQG9X5
For each step the following formula is generated:
= Web.Contents(Link_getToken,
= Json.Document(GetJson)
= Record.ToTable(FormatAsJson)
= #"Converted to Table"{0}[Value]
= #table(1, {{access_token_result}})

Which results in the overaal result:
https://ibb.co/qJTz8bM

This means a table is created? If so, this can be undone by removing the last formula (= #table(1, {{access_token_result}}))?
best response confirmed by Nemesis_3 (Copper Contributor)
Solution

@Nemesis_3 If your final step of the Token query looks similar to this:

 

2021-11-05_17-13-25.png

Then all it takes is a right-click and choosing "Drill down":

2021-11-05_17-14-02.png

Which will end up looking like this:

2021-11-05_17-15-46.png

 

Ha, that one works pretty well! Now the following problem pops up, regarding credentials in the second query (https://ibb.co/SXRNHcn)... Most likely not related to the problem as posted I think, or do you see any relation (and / or, solution)? The weird thing is, the same M code in Power BI works like a charm...

You probably have to refresh the access token query manually once.

@Jan Karel Pieterse Thank you for your reply. Unfortunately, this doesn't work.

 

Within the first query, the token which is used in the second query is retreived. This works like a charm now. See below the part of query 1 where the username and password for the API is given:

 

 

 

 

GetJson=Web.Contents(Link_getToken, 
[ 
   Headers=[#"Content-Type"="application/json"],
   Content=Text.ToBinary(
      "{ ""_username"": """&Username&""", ""_password"": """&Password&"""}"
   )
]
),

 

 

 

 

 

Now, in query 2, only the token as retrieved by query 1 is given:

 

GetJson_labels=Web.Contents(Link_getLabel,
[ 
   Headers=[#"Content-Type"="application/json",
   Authorization=" XXX "& Access_token
   ]

]

 

In here, there's no username and password provided. I guess (but I don't know for sure) in every query the password and username has to be provided when the API is called?

 

So I tried the following, adapting query 2:

 

 

GetJson_labels=Web.Contents(Link_getLabel,
[ 
   Headers=[#"Content-Type"="application/json",
   Authorization=" XXX "& Access_token
   ],
   Content=Text.ToBinary(
      "{ ""_username"": """&Username&""", ""_password"": """&Password&"""}"
   )

]
),

 

 

 However, this seems not the way an username an password has to be presented, as Excel shows: "DataSource.Error: Web.Contents failed to get contents from 'https:xxx' (405): Method Not Allowed"

 

Do you need the username and password in every query calling the API, even when the queries are sequentially? And if so, what am I doing wrong here (according to Excel... :-))?

I don't know for the API you're using. The one I have only requires the username and password to get the token, subsequent queries use that token.
1 best response

Accepted Solutions
best response confirmed by Nemesis_3 (Copper Contributor)
Solution

@Nemesis_3 If your final step of the Token query looks similar to this:

 

2021-11-05_17-13-25.png

Then all it takes is a right-click and choosing "Drill down":

2021-11-05_17-14-02.png

Which will end up looking like this:

2021-11-05_17-15-46.png

 

View solution in original post