undefinsplit column by delimitered

[mabdelrahman76]

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 Department's values should go into column "Department" and Application's values should got into column"Application and etc..

Row No.1:
Application: Value1, Request: Value2, Department: Value3, PatchingGroup: Value4

Row No.2:

Department: Value1, Application: Value2, Request: Value3

[Anonymous]

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]

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

[ziying35]

@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]

@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]

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

[edhans]

Hi @mabdelrahman76 - I did this 100% through the UI with the code below.
Turns this:

into this:

Steps are:

1. Split by comma so we get the fields split out.
2. Added an index column. This will keep the rows together for later.
3. Selected the Index column and unpivoted the other columns.
4. Trimmed the text. That may have been an artifact of copying your data in. Still good practice to make sure field names are not padded with spaces later.
5. Split the values column by the colon.
6. Kept only the index column, and the columns that will be my new column titles and values.
7. Pivoted the column that I want to use as the field names
8. Removed the index column. No longer needed.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyMlMTizJzM+zUghLzClNNdRRCEotLE0tLoEKGOkouKQWJBaV5KbmwcSMdRQCEkuSMzLz0t2L8ksLoMImSrE60UoYqoFGYtpjhG6PsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Added Index" = Table.AddIndexColumn(#"Split Column by Delimiter", "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Text.Trim, type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Value", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value.1]), "Value.1", "Value.2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[ziying35]

Hi, @mabdelrahman76 

This requirement is not difficult, I'll write a code for you in the morning

[Greg_Deckler]

@mabdelrahman76 - Yeesh! Maybe @ImkeF  or @edhans