Hi thereI am new to PowerBI and learning as I go. I am using PowerBI to query a REST API, and this is all working correctly. I have created a measure which again works correctly, however I have created a new entry in one of the current fields (in the API backend) that I pull from the API (which incidently was working correctly) which on refreshing the data, selecting the column then 'load more' I get the following: [Expression.Error] We cannot convert a value of type List to type Record. The field before had some simple text entries in it and I have added another entry - again text based - nothing complex. Could some steer me as to the nature of why I am getting this error please so I can understand how to fix it? Thanks

Hello Anonymous this could get nasty and without nowing the datasource (data elaborated by the getpage-function) is almost inpossible to help. If your Json-data however comes in formated in a way data for some field you have a record and list, you could make a Table.TransformColumns with Table.ExpandTableColumn instead of usind only Table.ExpandRecordColumm. Here a practicable example let Source = Json.Document("[{""a"":2},[""a;2""]]#(cr)#(lf)"), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), TransformColumns = Table.TransformColumns ( #"Converted to Table", { { "Column1", each if Value.Is(_, type list) then Table.FromRecords({Record.FromList({List.Transform(_, each Text.Split(_,";")){0}{1}},{List.Transform(_, each Text.Split(_,";")){0}{0}})},null,MissingField.Error) else if Value.Is(_, type record) then Table.FromRecords({_},null,MissingField.Error) else null } } ), #"Expanded Column1" = Table.ExpandTableColumn(TransformColumns, "Column1", {"a"}, {"a"}) in #"Expanded Column1" Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)Kudoes are nice tooHave funJimmy

Well, that code is expecting a record via the Table.FromRecords function, and if your data is returning a list it will fail. Perhaps ask the OP of that post since you and that person seem to be accessing the same data. Perhaps Jimmy801 has an idea beyond the try/otherwise construct that will fail, but even though I can convert a list to a table, I cannot do it blindly not knowing what is in the list and how to handle the resulting data, and to get to mirror how the records look, also being unable to see them.

Absolutely Anonymous- always good to give kudos to posts that are helpful. 👍😁 sorry couldn't be more help. It is difficult to troubleshoot shoot errors on a forum and even more so when it is the result of nested tables, lists, and records.

That error is coming from Power Query when you are loading data, and somewhere either in your M code or source data, you are getting a list of data and are then trying to handle it like a record, thus the error. Go into Power Query and look at the query that should now have a yellow triangle next to it and see if you can find the source of the error. We'd need screenshots to really help. If you find a "cell" that has the word "Error" in it, please click on the white area next to the word Error (not on the word Error itself) and give us a usable screenshot of what is appearing in the small window at the bottom of the query in yellow.

Hi thanks for the reply, I check the query editor no steps have a yellow triangle against them. I am selecting the columnThen selecting the drop downI click on 'Load more' then I recieve the error Hope that helps

[Expression.Error] We cannot convert a value of type List to type Record.

21 Replies

edhans
Community Champion
5 years ago
That error is coming from Power Query when you are loading data, and somewhere either in your M code or source data, you are getting a list of data and are then trying to handle it like a record, thus the error.

Go into Power Query and look at the query that should now have a yellow triangle next to it and see if you can find the source of the error. We'd need screenshots to really help. If you find a "cell" that has the word "Error" in it, please click on the white area next to the word Error (not on the word Error itself) and give us a usable screenshot of what is appearing in the small window at the bottom of the query in yellow.
- Anonymous
  Not applicable
  5 years ago
  Hi thanks for the reply, I check the query editor no steps have a yellow triangle against them. I am selecting the column
  Then selecting the drop down
  I click on 'Load more' then I recieve the error
  
  Hope that helps
  - edhans
    Community Champion
    5 years ago
    That is because in your data, beyond the first few hundred rows it initially looks at, there is a List embedded where it expects a Record. When you click Load More, it reads the entire data set until:
    
    it gets 1,000 values for the filter, at which point it stops
    
    It reads all of the records and populates the filter with what it found, not to exceed 1,000 records.
    
    Yours stops when it encounters an error. It may not even be in that field. It just cannot process more.
    
    On this table, tell it to Keep Errors.
    
    The do a Refresh All so it reads everything. It should show you the record(s) that have the issue.

Forum Discussion

[Expression.Error] We cannot convert a value of type List to type Record.

21 Replies

Related content

Expression.Error: We cannot convert the value "[Record]" to type List. Details: Value=[Record]

[Expression.Error] cannot convert null value to Logical

Expression.Error: We cannot convert a value of type Record to type Text.

Request: Expression.Error: The Field of the record wasn't found

ExceptionMashupException.Error: Expression.Error: We cannot convert a value of type Table to Record