cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Highlighted
Community Support
Community Support

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

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

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

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

Highlighted
Community Support
Community Support

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

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

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

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]

 

 

Highlighted
Community Support
Community Support

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

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Regular Visitor

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

Worked perfectly. Thanks for your help on this.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors