Forum Discussion
How to expand column of mixed data
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.
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 ToTableNotes:
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] )
6 Replies
krishna_murthyHelper II
Mauro89 thanks you for info....
nc1985 :- Here the one more opiton we can explore to do this
Step 1: Add a Custom ColumnGo 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:
You’ll see a new column with Table values in every row.
Click the expand icon (double-arrow).
Expand into columns.
- Mauro89
Super User
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 2Split 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 - 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 ToTableNotes:
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] ) 
Shubham_rai955You 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
Add a custom column:
= if Value.Is([Answers], type list) then Record.FromList([Answers], {"Value"}) else [Answers]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
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 nullExpand that custom column, then expand inner columns as needed.
Both ways let you pull the values out of the mixed column in simple steps.
v-karpurapudCommunity Support
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- 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.
