cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Unable to convert a value of type List in the Record Type in Query

Hi

I am trying to get records from а web-service

Using this code I have got an error

 

Unable to convert a value of type List in the Record Type.

http://prntscr.com/drhibr

 

let
    #"campaignsId_List"= Table.FromList(Table.Column(campaigns, "campaignId"), null, {"campaignId"}, null, null),

    Ссылка = "campaigns/statistics/balance?query[campaignId]=PGp8n",

    #"Запрос http" = Table.AddColumn(#"campaignsId_List", "responseJson", each Record.ToTable( Json.Document(Web.Contents(URL & Ссылка & [campaignId], [Headers=[#"X-Auth-Token"="api-key "& Token, #"X-Domain"= Domain , #"Content-Type"="application/json"]]))))
in
    #"Запрос http"

What is а cause of this error?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Unable to convert a value of type List in the Record Type in Query

Had a closer look at your code and to me it looks as if you need to remove Errors from your column "responseJSON" (check the column -> righclick with mouse -> select "Remove Errors".

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Unable to convert a value of type List in the Record Type in Query

It could be that the Json.Document doesn't return a record, but a list instead.

Try the expression without the "Record.ToTable"-conversion and check the format of the returned Json.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Helper III
Helper III

Re: Unable to convert a value of type List in the Record Type in Query

ImkeF, Thank you
This error was because Record.ToTable have gotten null-string
Is there any way to exclude null-records?
Highlighted
Super User IV
Super User IV

Re: Unable to convert a value of type List in the Record Type in Query

Yes, you can use if .. then (for conditions) or try ... otherwise (as the error-handler).

What exactly shall happen: Skip the step & continue with the previous step instead or return a default-value?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Helper III
Helper III

Re: Unable to convert a value of type List in the Record Type in Query

ImleF, I need use "Skip the step & continue with the previous step"

Here is screen http://prntscr.com/drxkgm

 

Please help add this check to the code

 

let
    #"campaignsId_List"= Table.FromList(Table.Column(campaigns, "campaignId"), null, {"campaignId"}, null, null),

    Ссылка = "campaigns/statistics/balance?query[campaignId]=",

    #"Запрос http" = Table.AddColumn(#"campaignsId_List", "responseJson", each Record.ToTable( Json.Document(Web.Contents(URL & Ссылка & [campaignId], [Headers=[#"X-Auth-Token"="api-key "& Token, #"X-Domain"= Domain , #"Content-Type"="application/json"]]))))
in
    #"Запрос http"
Highlighted
Super User IV
Super User IV

Re: Unable to convert a value of type List in the Record Type in Query

Had a closer look at your code and to me it looks as if you need to remove Errors from your column "responseJSON" (check the column -> righclick with mouse -> select "Remove Errors".

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Highlighted
Helper III
Helper III

Re: Unable to convert a value of type List in the Record Type in Query

ImkeF, yes, it works!

Thank You 🙂

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors