Forum Discussion

adam_cogswell's avatar
adam_cogswell
Regular Visitor
9 years ago
Solved

JSON query based on another query

Hi,   New to PowerBI, and having what is probably a simple problem but can't seem to get it figured. I am trying to load JSON data and I've got one query working like a charm, transformed into a ta...
Need Help
  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    9 years ago

    Sorry I ommitted a "let".  See below:

     

    let function = (ID as text) => let
    Source = Json.Document(Web.Contents("https://api.provider.com/survey_results?survey_id=" & ID,
        [Headers=[Authorization="Bearer ACCESS_TOKEN", Allow="application/json", #"NGIN-API-VERSION"="0.1"]]))
in
    Source
in
    function
dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago

For your second table (SurveyResults), in your web call I'm assuming that the [ID] field is a column of id's in the first table?

 

You can convert your second query to a funciton which you then invoke (via Add Column) and it will read each [ID] value from the [ID] field and run the web call.

 

If this scenario sounds correct, I can give you more details on how to implement, although it is very simple and can be done almost entirely via the UI.

  • adam_cogswell's avatar
    adam_cogswell
    Regular Visitor
    9 years ago

    Hi, 

     

    You're correct. The ID field is a column of IDs in the first table. I'd love to hear how to accomplish this. Thanks. 

    • dkay84_PowerBI's avatar
      dkay84_PowerBI
      Microsoft Employee
      9 years ago

      A good general overview is here:

       

      http://radacad.com/custom-functions-made-easy-in-power-bi-desktop

       

       

      I usually follow the pattern of just changing my M code to indicate it is function and passing in a value.  For example, change your M code for the second query to the following:

       

      let function = (ID as text) =>
    Source = Json.Document(Web.Contents("https://api.provider.com/survey_results?survey_id=" & ID,
        [Headers=[Authorization="Bearer ACCESS_TOKEN", Allow="application/json", #"NGIN-API-VERSION"="0.1"]]))
in
    Source
      in
          function

      Then, in your original query/table with the list of IDs, go to Add Column > Invoke Custom Function, choose the function you just made as the function query, and change the table drop down from text to column name and select the [ID] column.

       

      This should then run your API call for every line in your table, plugging in the ID field for each row into the function and return the result.  Note, you will have to expand the result column by clicking on the expand icon in header of the column.

      • adam_cogswell's avatar
        adam_cogswell
        Regular Visitor
        9 years ago

        Hi,

         

        Thanks for the reference information, and the M code. I was trying to use your code just to get more familiar with the language, but it's throwing a "Token EoF expected." error when I try to save it. Seems to dislike the two "in" statements at the end:

         

        let 
    function = (ID as text) =>
    Source = Json.Document(Web.Contents("https://api.provider.com/survey_results?survey_id=" & ID,
        [Headers=[Authorization="Bearer ACCESS_TOKEN", Allow="application/json", #"NGIN-API-VERSION"="0.1"]]))
in
    function
in
    Source