Nov 05 2021 07:30 AM
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"
Nov 05 2021 08:30 AM
Nov 05 2021 08:57 AM
Nov 05 2021 09:16 AM
Solution@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:
Nov 05 2021 09:43 AM
Nov 05 2021 12:10 PM
Nov 06 2021 10:59 AM - edited Nov 07 2021 08:06 AM
@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... :-))?
Nov 06 2021 02:59 PM
Nov 05 2021 09:16 AM
Solution@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: