Forum Discussion

Nemesis_3's avatar
Nemesis_3
Copper Contributor
Nov 05, 2021
Solved

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...
BI & Data Analysis
excel
Formulas and Functions
  • JKPieterse's avatar
    JKPieterse
    Nov 05, 2021

    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:

     

Nemesis_3's avatar
Nemesis_3
Copper Contributor
Nov 05, 2021
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}}))?
JKPieterse's avatar
JKPieterse
Silver Contributor
Nov 05, 2021

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:

 

  • Nemesis_3's avatar
    Nemesis_3
    Copper Contributor
    Nov 05, 2021
    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...

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Nov 05, 2021
      You probably have to refresh the access token query manually once.
      • Nemesis_3's avatar
        Nemesis_3
        Copper Contributor
        Nov 06, 2021

        JKPieterse 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://fds2.fdta.nl/v1/label' (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... :-))?

Resources