Forum Discussion

howie2293's avatar
howie2293
Frequent Visitor
6 years ago
Solved

Pull Rest API Query based on existing column data as key Search item

Hi


Trying to be able to pull from an additional data source using a Rest API which is returning the data in Json format. What I am trying to do is based on the item in one of my existing columns. The API REST query is run, and it populate the results in further columns. Has anyone been able to accomplish this?

The REST query is " http://Server//DataServices/v1/Search/serviceLookup?q=serviceInstanceId:%(Service Identifier ID)%"
Then the column to match this this query to is "Service Identifier ID"


Thanks in advance.

 

 

 

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi howie2293 ,

     

    It seems you are trying to create a new query, please try to create a custom column and use the previous code in it. such as your previous code is :

     

    let
  Source = step1...,
  Step2Name = step2...,
  Step3Name = Step3...
in
  Step3Name

     

    Then there are a Service Identifier ID column in the table, such as following:

     

    Service Identifier ID Column2.. Column3.. Column4..
    1 ... ... ...
    2 ... ... ...

     

    Then we cretea a custom column, it will add a additional step:

     

    let
  Source = step1...,
  Step2Name = step2...,
  Step3Name = Step3...,
  #"Add Column" = Table.AddColumn(Step3Name,"Custom", 
       each let
               SIID = [Service Identifier ID]
            in
              Json.Document(
              Web.Contents(
                "http://Server//DataServices/v1",
                [ RelativePath="/Search/serviceLookup?", 
                Query=["q"="serviceInstanceId:" & SIID], 
                Headers=["Authorization"= "Bearer " & "If_You_Use_Bearer_Token_Put_it_Here"]
                ]
             )
           ))
in
  #"Add Column"

     

    The table will looks like:

     

     

    Service Identifier ID Column2.. Column3.. Column4.. Custom
    1 ... ... ... [Json1]
    2 ... ... ... [Json2]

     

    Change the [Service Identifier ID] to the name of your column, in the query.

     


    Best regards,

     

6 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi howie2293 ,

     

    Please try to create a custom column by following M query: 

     

    let
  SIID = [Service Identifier ID]
in
  Json.Document(
    Web.Contents(
      "http://Server//DataServices/v1",
      [ RelativePath="/Search/serviceLookup?", 
        Query=["q"="serviceInstanceId:" & SIID], 
        Headers=["Authorization"= "Bearer " & "If_You_Use_Bearer_Token_Put_it_Here"]
      ]
    )
  )

     

    Then expand the new rows to get result for each [Service Identifier ID]


    Best regards,

     

    • howie2293's avatar
      howie2293
      Frequent Visitor
      6 years ago

      I have just followed what you have advised but I am getting an invalid Identifer for "q" within "Query=["q"="serviceInstanceId:" & "SIID]"

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi howie2293 ,

         

        Sorry for my mistake in formula, please try to use following:

         

        let
  SIID = [Service Identifier ID]
in
  Json.Document(
    Web.Contents(
      "http://Server//DataServices/v1",
      [ RelativePath="/Search/serviceLookup?", 
        Query=[q="serviceInstanceId:" & SIID], 
        Headers=["Authorization"= "Bearer " & "If_You_Use_Bearer_Token_Put_it_Here"]
      ]
    )
  )

         


        Best regards,