How to expand column of mixed data

[nc1985]

Hi,

I have this column with records, lists and empty cells.  I can't expand the column like I could if it was just all lists or all records.  So how do I get the data out of the lists and records?

Thanks you.

[v-karpurapud]

Hi @nc1985 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

[Shubham_rai955]

You cannot expand that column directly because it mixes records, lists, and nulls in one field.​

Simple idea

First turn everything into records (or into tables), then expand.

Option 1 – Convert lists to records, then expand

1. Add a custom column:

   = if Value.Is([Answers], type list) then Record.FromList([Answers], {"Value"}) else [Answers]

2. Now the new column is all records or null, so you can use the normal expand button on it.

Option 2 – Convert the whole column to a table

1. Add a custom column:

   = if Value.Is([Answers], type record) then Record.ToTable([Answers]) else if Value.Is([Answers], type list) then Table.FromList([Answers]) else null

2. Expand that custom column, then expand inner columns as needed.

Both ways let you pull the values out of the mixed column in simple steps.

[MarkLaf]

I'm guessing that the lists are lists of records of same structure as the records at top level of the column? If yes, then the trick is to 1) remove blanks, 2) wrap single records into a 1-item list, 3) combine all lists of records into a single list of records, 4) convert your list of records into a table with Table.FromRecords.

I'll walk through an example.

Let's say we are starting with something like the below where we have a mix of single and lists of records of general structure [Field1=text,Field2=text] :

Table.FromColumns(
    {{
        [Field1="a",Field2="b"],
        [Field1="c",Field2="d"],
        {
            [Field1="e",Field2="f"],
            [Field1="g",Field2="h"]
        },
        [Field1="i",Field2="j"],
        "",
        "",
        [Field1="k",Field2="l"]
    }}, 
    type table [Answers=any]
)

Sample

The below M code will perform the steps I outlined at top:

let
    Source = Sample,
    RemoveText = List.Select( Source[Answers], each not ( _ is text ) ),
    WrapSingleRecords = List.Transform( RemoveText, each if _ is list then _ else {_} ),
    CombineRecordLists = List.Combine( WrapSingleRecords ),
    ToTable = Table.FromRecords( CombineRecordLists, type table [ Field1=text, Field2=text ] )
in
    ToTable

Notes:

1) If the lists are totally different kind of data, then you'll need to think about and articulate what is the common info if any that you want to extract from the lists/records and then perform conditional transform (eg: if _ is record then <record-specific transform> else if _ is list then <list-specific transform> else null/fallback)

2) If you are dealing with records inside the lists but the records are not uniform (ie different fields are present) then I'd recommend using the following as an alternative in the ToTable step. This will give you a record column that you can then expand (can use "Load more" button in expand UI to explore all fields)

ToTable = Table.FromColumns( { CombineRecordLists }, type table [Answers=record] )​

[v-karpurapud]

Hi @nc1985 

I would also take a moment to thank  @Mauro89 and @krishna_murthy  for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. 

I hope the information provided help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

Best Regards, 
Community Support Team  

[krishna_murthy]

@Mauro89 thanks you for info....

@nc1985 :- Here the one more opiton we can explore to do this 
Step 1: Add a Custom Column

Go to:
Add Column → Custom Column
Use this M code:
= let
v = [Answers]
in
if v = null then #table({}, {}) // empty table
else if Value.Is(v, type record) then Record.ToTable(v)
else if Value.Is(v, type list) then Table.FromList(v, Splitter.SplitByNothing(), {"Value"})
else #table({}, {}) // fallback

This converts every row to a table object.

Step 2: Expand the new column

After adding the column:

1. You’ll see a new column with Table values in every row.

2. Click the expand icon (double-arrow).

3. Expand into columns.
   
   
   

[Mauro89]

Hi @nc1985,

I think of two options to do that.

Option 1:

You can expand a mixed Record/List/Null column in Power Query, but you must first normalize the column so every row becomes a Record. Once everything is a Record, Power Query will allow expansion.

Here is the cleanest approach:

Step 1 — Convert Lists to Records

Add a custom column:

= Table.AddColumn( Source, "Normalized", each if Value.Is([Answers], type list) then Record.FromList([Answers], {"Col1","Col2","Col3"}) else if Value.Is([Answers], type record) then [Answers] else null )

If you don’t know the number of list items or want them unnamed, use this instead:

= Table.AddColumn( Source, "Normalized", each if Value.Is([Answers], type list) then Record.FromList([Answers], List.Transform({0..List.Count([Answers])-1}, each "Item"&Text.From(_))) else if Value.Is([Answers], type record) then [Answers] else null )

Now all rows contain either a Record or null.

Step 2 — Expand the Normalized column

Use the expand icon on Normalized.
Power Query will let you expand normally because all rows now have the same data type.

Alternative quick method

If you only want the raw values, you can also use:

Table.TransformColumns(Source, {"Answers", each if Value.Is(_, type list) then Text.Combine(List.Transform(_, Text.From), ",") else _})

But the normalized-record method is the most reliable if you need structured fields.

Option 2

Split the column "Answers" into two columns. One for lists and the other for records. Then process them seperatly and merge them together if processed.

If this helps, leave a kudos or mark it as solution.

Best regards, Mauro