Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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,
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,
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,
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,
Worked perfectly. Thanks for your help on this.
User | Count |
---|---|
15 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
18 | |
11 | |
5 | |
4 | |
3 |