Forum Discussion

mabdelrahman76's avatar
mabdelrahman76
Icon for Helper I rankHelper I
6 years ago

undefinsplit 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...
ziying35's avatar
ziying35
Icon for Impactful Individual rankImpactful Individual
6 years ago

mabdelrahman76 

 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUnIsKMjJTE4syczPs1IIS8wpTTXUUQhKLSxNLS6BChjpKLikFiQWleSm5sHEjHUUAhJLkjMy89Ldi/JLC6DCJkq1OsimY+gDGo5poxG6jcZKtbEA",BinaryEncoding.Base64),Compression.Deflate))),
    fx = (str)=> Expression.Evaluate("["&Text.Replace(Text.Replace(str,": ","="""),",",""",")&"""]"),
    toList = List.Buffer(Table.ToList(Source, each _{0})),
    acc = List.Accumulate(toList, {{},[]}, (s,c)=>let rec=fx(c) in {s{0}&{rec}, s{1}&rec}),
    result = Table.FromRecords(acc{0}, Record.FieldNames(acc{1}), MissingField.UseNull)
in
    result

 

ziying35's avatar
ziying35
Icon for Impactful Individual rankImpactful Individual
6 years ago

mabdelrahman76 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUnIsKMjJTE4syczPs1IIS8wpTTXUUQhKLSxNLS6BChjpKLikFiQWleSm5sHEjHUUAhJLkjMy89Ldi/JLC6DCJkq1OsimY+gDGo5poxG6jcZKtbEA",BinaryEncoding.Base64),Compression.Deflate))),
    fx = (str)=> Expression.Evaluate("["&Text.Replace(Text.Replace(str,": ","="""),",",""",")&"""]"),
    toRecs = Table.ToList(Source, each fx(_{0})),
    result = Table.FromRecords(toRecs, Record.FieldNames(Record.Combine(toRecs)), 2)
in
    result

I provide another method, this method is just learned from a group of PQ bigwigs, it can handle tens of thousands of rows of data with a high degree of efficiency, the previous method I provided can only handle about 2000 rows of data volume

  • mabdelrahman76's avatar
    mabdelrahman76
    Icon for Helper I rankHelper I
    6 years ago

    ziying35 , thank you very much, i will try and let you know in case i need further help.