Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

8 REPLIES 8
Anonymous
Not 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)

 

 

 

 

image.png

 

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)

 

 

 

 

 

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

ziying35
Impactful Individual
Impactful Individual

@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
Impactful Individual
Impactful Individual

@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

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

edhans
Super User
Super User

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

edhans_0-1598026063585.png

into this:

edhans_1-1598026086699.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
ziying35
Impactful Individual
Impactful Individual

Hi, @mabdelrahman76 

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

Greg_Deckler
Super User
Super User

@mabdelrahman76 - Yeesh! Maybe @ImkeF  or @edhans 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors