Forum Discussion

lily99's avatar
lily99
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 bod...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    lily99 , you might want to try

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

lily99's avatar
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's avatar
    CNENFRNL
    Community Champion
    5 years ago

    lily99 , you might want to try

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

    • lily99's avatar
      lily99
      Regular Visitor
      5 years ago

      Thanks so much CNENFRNL . It worked perfectly!