Forum Discussion
mabdelrahman76
Helper I
6 years agoundefinsplit column by delimitered
Hi, I would like to split column by delimiter "," for below many rows, here two rows as example. the output should be in correct order for each values based on the new column name. for instance Depa...
Anonymous
6 years agoNot applicable
I adapted the svcript of an older similar subject
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUnIsKMjJTE4syczPs1IIS8wpTTXUUQhKLSxNLS6BChjpKLikFiQWleSm5sHEjHUUAhJLkjMy89Ldi/JLC6DCJkq1OsimY+gDGo5poxG6jcZKtbEA",BinaryEncoding.Base64),Compression.Deflate))),
ac = Table.AddColumn(Source, "Custom",
each Record.FromList(
List.Transform(List.Alternate(Text.SplitAny(_[Column1],":,"),1,1,0),Text.TrimStart),
List.Transform(List.Alternate(Text.SplitAny(_[Column1],":,"),1,1,1),Text.TrimStart)
)
),
fields=List.Union(List.Transform(ac[Column1], each List.Transform(List.Alternate(Text.SplitAny(_,":,"),1,1,1),Text.TrimStart)))
in
Table.FromRecords(ac[Custom],fields,MissingField.UseNull)
written in a perhaps more orderly manner:
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUnIsKMjJTE4syczPs1IIS8wpTTXUUQhKLSxNLS6BChjpKLikFiQWleSm5sHEjHUUAhJLkjMy89Ldi/JLC6DCJkq1OsimY+gDGo5poxG6jcZKtbEA",BinaryEncoding.Base64),Compression.Deflate))),
selFieldOrValues = (string, fOv) => List.Transform(
List.Alternate(Text.SplitAny(string, ":,"), 1, 1, fOv),
Text.TrimStart
),
ac = Table.AddColumn(
Source,
"Custom",
each Record.FromList(selFieldOrValues([Column1], 0), selFieldOrValues([Column1], 1))
),
fields = List.Union(List.Transform(ac[Column1], each selFieldOrValues(_, 1)))
in
Table.FromRecords(ac[Custom], fields, MissingField.UseNull)
mabdelrahman76
Helper I
6 years agoAnonymous ,thank you very much, i will try and let you know in case i need further help.