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. ...
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,

     

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,

       

      • howie2293's avatar
        howie2293
        Frequent Visitor
        6 years ago

        HI

         

        That has resolved that part of the of the script. I now come to the SIID part of this where when I have it

         

        let
        #"Query1 (2)" = let
        Source = Query1,
        SIID= [SERVICE_IDENTIFIER_ID]

         

        I get the expression error "Expression.Error: The name 'SIID' wasn't recognized. Make sure it's spelled correctly."

         

        Then if I use the following

         

        let
        SIID= #"Query1 (2)"[SERVICE_IDENTIFIER_ID],
        #"Query1 (2)" = let
        Source = Query1

         

        I have the below Expression error message

        "Expression.Error: We cannot apply operator & to types Text and List.
        Details:
        Operator=&
        Left=serviceInstanceId:
        Right=[List]