Forum Discussion

Regular Visitor

5 years ago

Solved

API call a list in M query

Hello,

I want to build a Body of a REST API call using M Query in PowerBI.

I have a list of ID from a list named "UserID" that I want to use it as a variable in the body

Here is my script

body = "{""ids"":" & List.Distinct(UserID) & "}",

When I executed it, it gave me an errors:

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

How can I handle this error? what can be a workaround?

Thanks for your help.

lily99 , you might want to try

body = "{""ids"":[" & Text.Combine(List.Transform(List.Distinct(UserID), each """" & _ & """"),",") & "]}"

4 Replies

ImkeF
Community Champion
5 years ago
Hi lily99 ,

if you want to pass a string into the body, you'd have to transform the list into a string as well. Meaning, concatenate the values by comma for example:

body = "{""ids"":" & Text.Combine(List.Distinct(UserID), ",") & "}",
lily99
Regular Visitor
5 years ago
Hello ImkeF
Thanks for your suggestion, I applied it.
body = "{""ids"":[""" & Text.Combine(List.Distinct(UserID),",") & """]}"

and get the result
{"ids":["User1,User2,User3,User4"]}

However, I want to have the result like this:
{"ids":["User1","User2","User3","User4"]}

so I applied extra "" around the list
= "{""ids"":[" & Text.Combine({"""",List.Distinct(UserID),""""},",") & "]}"
and I got this error:
Expression.Error: We cannot convert a value of type List to type Text.
Details:
Value=[List]
Type=[Type]

How can I make my body return like this?
{"ids":["User1","User2","User3","User4"]}

Thanks alot
- CNENFRNL
  Community Champion
  5 years ago
  lily99 , you might want to try
  
  body = "{""ids"":[" & Text.Combine(List.Transform(List.Distinct(UserID), each """" & _ & """"),",") & "]}"
  - lily99
    Regular Visitor
    5 years ago
    Thanks so much CNENFRNL . It worked perfectly!