Forum Discussion
Solved
Multiple column split in one step
Hi All
I am trying to split multiple columns in just one step. I did try several solutions but none did work for me.
I guess I need to use a list.
I have 3 columns, and all 3 should be splitting with different combinations of delimiters, but something like this
Left table is what I need to split to get the table on the right.
pls try this code
let f= (z)=>[ a = Record.ToList(z), b = List.Transform(a, (x)=> Splitter.SplitTextByAnyDelimiter({"(",")"})(x)), c = List.Combine( List.Transform(b,(x)=> List.RemoveMatchingItems(x,{"",null}))), e = Table.FromRows({c}) ][e], Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WStQw0lTSUUrSMAZRyRomJppKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}), Custom1 = Table.Combine( Table.AddColumn( #"Changed Type","Newtbl",f)[Newtbl]) in Custom1
1 Reply
pls try this code
let f= (z)=>[ a = Record.ToList(z), b = List.Transform(a, (x)=> Splitter.SplitTextByAnyDelimiter({"(",")"})(x)), c = List.Combine( List.Transform(b,(x)=> List.RemoveMatchingItems(x,{"",null}))), e = Table.FromRows({c}) ][e], Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WStQw0lTSUUrSMAZRyRomJppKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}), Custom1 = Table.Combine( Table.AddColumn( #"Changed Type","Newtbl",f)[Newtbl]) in Custom1