Forum Discussion
Transform columns whose data type is "List"
I have a table with 100 columns
In which there are 40 columns (which data type is "list") need to be Transform by this code:
List.Transform(list_column_names_need_transform, (columnName) => Table.TransformColumns(Table, {{columnName, each Text.Combine(List.Transform(_, Text.From), ",")}})),
I want the result is 1 table with 60 columns without Transform and 40 columns after Transform.
The result I want to got is a table with 100 columns which didn't have any "list" data type column
I alse don't want to list every column I want to Transform in the code (Because if later the number of columns (which data type is "list") increases, I have to re-edit the code, which is very difficult.)
Here is an example of my table:
Please help me with this issue, Thanks everyone
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJWitWJVnICslyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Column3", each {"E", "F", "G"}), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Column4", each {"H", "I", "J"}), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "RecordColumn", each _), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"RecordColumn"}), #"Added Custom3" = Table.AddColumn(#"Removed Other Columns", "Custom", each let fieldnames = Record.FieldNames([RecordColumn]), updatedlist = List.Transform(Record.ToList([RecordColumn]), each try Text.Combine(_, ",") otherwise _), newrecord = Record.FromList(updatedlist, fieldnames) in newrecord), #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom3",{"Custom"}), #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Other Columns1", "Custom", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}) in #"Expanded Custom"Pat
3 Replies
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJWitWJVnICslyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Column3", each {"E", "F", "G"}), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Column4", each {"H", "I", "J"}), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "RecordColumn", each _), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"RecordColumn"}), #"Added Custom3" = Table.AddColumn(#"Removed Other Columns", "Custom", each let fieldnames = Record.FieldNames([RecordColumn]), updatedlist = List.Transform(Record.ToList([RecordColumn]), each try Text.Combine(_, ",") otherwise _), newrecord = Record.FromList(updatedlist, fieldnames) in newrecord), #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom3",{"Custom"}), #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Other Columns1", "Custom", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}) in #"Expanded Custom"Pat
Thank you very much, this is what I want to find!
I guess you could try to wrap your transformation code in a "try" statement.
Error handling - Power Query | Microsoft Learn
Or even better - make the list transformation code a function and then pass the columns to it.
