Forum Discussion
How to use the result of one query in another query?
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"
Nemesis_3 If your final step of the Token query looks similar to this:
Then all it takes is a right-click and choosing "Drill down":
Which will end up looking like this:
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}}))?Nemesis_3 If your final step of the Token query looks similar to this:
Then all it takes is a right-click and choosing "Drill down":
Which will end up looking like this:
