Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AutoJL
Helper I
Helper I

Column with List values. List items are records and cant extract them due to Expression.Error

Hi,

 

I am facing an issue in later steps of my data modeling in PowerQuery. After some transformations I am using the Json parse to extract some information from the Json messages.

 

Most of the Json parsing is ok up until I reach a point where one of the Json elements is a list and that list is made up of records, this is a problem for PowerQuery since it wont allow me to extract those values due to:

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

 

ColumnwithListelements.PNG

recordswithinlist.PNG

 

This list can contain many records ( although most likely wont get any bigger than 4 or 5) , and each record is a Json object with some elements (of which I only need 2 for now). My biggest issue is getting through the point were i cant extract those records as extra columns like in the rest of the parsing process ( using the option to expand on new lines will cause me to have duplicates and headaches since this table has many other columns and will use relations later).

 

There is a similar issue already reported:

https://community.fabric.microsoft.com/t5/Desktop/Expression-Error-We-cannot-convert-a-value-of-type...

 

But I cant get my head around the solution suggested there by @MarcelBeug as it is tailored to that specific case.

Can anyone help me understand what the issue really is and how to deal with it?

Many thanks in advance!

2 REPLIES 2
AutoJL
Helper I
Helper I

Hi @v-rongtiep-msft !

 

I think I understand the code you post but I dont think that works. I believe that is the code for expanding a records column ( that is what is automatically generated when hitting the expand icon on the top right of any records column). That works for the previous steps indeed.

 

The issue is my column is a list column and when trying to extract those values in the lists to new columns it gives the errors since it can only do that operation with text and apparently records cant be converted to text.

 

Also I dont know how many records are there in the lists some might have one, some 2 or could be more, theres no way to know it beforehand.

 

I think I actually solved this part by extracting the values onto new rows ( which created duplicates ) and then pivoting the columns ( which I think, not sure tho, returned the table to no duplicates and added the values on the extra rows back to columns).

 

Thanks for help anyways!

v-rongtiep-msft
Community Support
Community Support

Hi @AutoJL ,

The error message you are seeing is due to the fact that you are trying to convert a record to text. In Power Query, you can use the Table.ExpandRecordColumn function to expand the records in a list into separate columns. Here is an example of how to use this function:

let
    Source = Json.Document(JsonText),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"Column1", "Column2"}, {"Column1", "Column2"})
in
    #"Expanded Value"

 

I have also found a similar post, please refer to it to see if it helps you.

apply - POWER QUERY [Expression.Error] Cannot convert the value null to type Table - Stack Overflow

 

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details.

 

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.