Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
howie2293
Frequent 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

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
v-lid-msft
Community Support
Community Support

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.

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

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.

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]

 

 

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.

Worked perfectly. Thanks for your help on this.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.