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

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

howie2293 — Wed, 13 May 2020 13:06:57 GMT

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.

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

v-lid-msft — Thu, 14 May 2020 08:20:29 GMT

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,

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

howie2293 — Fri, 15 May 2020 09:04:56 GMT

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

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

v-lid-msft — Fri, 15 May 2020 09:11:34 GMT

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,

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

howie2293 — Fri, 15 May 2020 11:41:03 GMT

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]

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

v-lid-msft — Mon, 18 May 2020 00:41:53 GMT

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,

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

howie2293 — Wed, 20 May 2020 07:19:51 GMT

Worked perfectly. Thanks for your help on this.