Splitting of rows by duplicate

[ramhariessentia]

Hi all,

I am having a problem where i  am trying to split rows (Notes of an operator associated with a unique customer query number). Please note that each note as another unique id as well (just giving you something to ponder). I have customer query numbers repeating itself from 2 to 4 times but all the notes are in one column. I need to split the notes column into maximum of four so that i can look at issues associated with customer reference. 

[mussaenda]

Hi @ramhariessentia,

You can create an index based on the second column from your photo then pivot it with notes as your value.

In this, you can have 4 different columns (if ever the max count of each data in second column is 4).

If you need more help, provide a sample data where we can copy (not a screenshot).

Thank you.

Hope this helps!

[Anonymous]

I believe that if any of us understand what you are looking for, you are in luck.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZA9DsIwDEavYmWuEOKvdOUAXKB0MKnVWkrjkriciXtwMUzFEInBS96z/Tlt65r9wVXuKhFGAY8zq7iuKt+VIZPVzDTgHx2RYaKcBTD4JSIo3ikEXLXD8fTT/G3Zbnf1+wUcIHFegtosQFXKUrgXzjLYlCxBQCkqJlpxfW4M97xmTASPxbYIFMyPBJ6SHxlMK4CoJopkTT1++7Jd1KNyocxJnoyTeK5gQoiW2KIOmDCq/cjGdd0H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Query Number" = _t, Notes = _t]),
    #"Raggruppate righe" = Table.Group(Source, {"Customer Query Number"}, {{"all", each Table.AddIndexColumn(_,"idx",1,1)}}),
    #"Tabella all espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "all", {"Notes", "idx"}, {"Notes", "idx"}),
    #"Colonna trasformata tramite Pivot" = Table.Pivot(Table.TransformColumnTypes(#"Tabella all espansa", {{"idx", type text}}, "it-IT"), List.Distinct(Table.TransformColumnTypes(#"Tabella all espansa", {{"idx", type text}}, "it-IT")[idx]), "idx", "Notes")
in
    #"Colonna trasformata tramite Pivot"

[Jimmy801]

Hello @ramhariessentia 

you didn't exactly specify whats your exact outcome is. I just could immagine that you would like to have something more readable per request. Here an approach to connect all notes into one cell

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfJIzcnJB9KGSrE6EBHPmFIDAyPzXAWvzNzcSqCAEVzKNS9FIT9NIS+/JBXIMwaLO4ENqVSozC8FskzgYmmlOTkKBfmZeSVAjilY2BnJPjO4SHJiHkizQkZqToFCLshgc7icUxCQsIBz/SuAhKVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Query Number" = _t, Notes = _t, #"Note ID" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Customer Query Number"}, {{"Read Notes", each Text.Combine(Table.Sort(_, {{"Note ID", Order.Ascending}})[Notes], "#(lf)")}})
in
    #"Grouped Rows"

 transforms this

into this

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Payeras_BI]

Hi @ramhariessentia ,

Paste this code in a Blank Query using the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLLL0kF0YZKsToIESMgbYQiYgykjcEiRki6TFBEQLQpWMQYSY0ZighIjTmKCIi2QBExAdKWSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Query Number" = _t, Notes = _t, #"Note ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Query Number", Int64.Type}, {"Notes", type text}, {"Note ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer Query Number"}, {{"All", each _, type table [Customer Query Number=number, Notes=text, Note ID=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Notes", each [All][Notes]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Notes", each Text.Combine(List.Transform(_, Text.From), "||"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Notes", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), {"Notes.1", "Notes.2", "Notes.3", "Notes.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Notes.1", type text}, {"Notes.2", type text}, {"Notes.3", type text}, {"Notes.4", type text}})
in
    #"Changed Type1"

With this you go from this:

To this: