Extract distinct values from a list in sequential manner - M/Power query

[Anonymous]

Hi all,

First, Thanks for help. I am stuck on an issue. 

Something like below - first col is input col (everything is dynamic - basically if a value in col 1 has appeared say "A" unless it is interrputed by any other value say "S14" i should get its distinct (only once) and if it occurs again after "S14" then i should get that and in seq. Example is below) and second col is the output format i need. Again any help much appreciated.

Quality (Input col)                                                                                                                       
["A", "A", "A", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14"]         
["A", "K05, "A", "S14", "S14", "S14", "S14", "S14", "K05", "S14", "S14", "S14", "S14", "S14"]         
["A", "A", "A", "S14", "A", "S14", "S14", "S45", "S14", "S14", "S14", "A", "K05", "A"]                     
["A", "A", "A", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "A"]               

Distinct Sequential Quality (Output col) - corrsponding to the above rows. (sorry it wont let me paste in the table format)
A, S14
A, K05 , A, S14, K05, S14
A,S14, A, S14, S45, S14,A,K05,A
A,S14,A

@Jakinta  Please help if you are free. I am creating this new post for other users to get help too if they have similar problem : ) 

[Jakinta · Accepted solution]

Here it is with @AlexisOlson 's List.Accumulate version.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wio5RcoxR0lFAp4INTWjJiFWAAaVYHWRXeBuYkusKoFZquQJHWOB2lQlhqx3RXOmIbD8yIM4tVGdgcY9SbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quality = _t]),
    Split = Table.AddColumn(Source, "Custom", each Text.Split(Text.Select([Quality],{"0".."9","A".."Z",","}), ",")),
    Accumulate = Table.AddColumn(Split, "Sequential", each List.Accumulate ( [Custom], {}, (s,c)=> if List.Last(s&{})=c then s else s&{c} )),
    Removed = Table.SelectColumns(Accumulate,{"Sequential"}),
    Extracted = Table.TransformColumns(Removed, {"Sequential", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    Extracted

[Anonymous]

@Jakinta  Thanks you for your time again. Your solution works completely fine this time as well.

@AlexisOlson  Thanks to you too Alexis, the latest solution you gave works perfectly fine too. 🙂 

Really appreciate the help from both. 

[mahoneypat]

Here's another way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wio5RcoxR0lFAp4INTWjJiFWAAaVYHWRXeBuYkusKoFZquQJHWOB2lQlhqx3RXOmIbD8yIM4tVGeA3KMUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Quality (Input col)                                                                                                                       " = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Quality (Input col)                                                                                                                       ", "Input"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Text.Combine(List.Distinct(List.Transform(Text.Split(Text.BetweenDelimiters([Input], "[", "]"), ","), each Text.BetweenDelimiters(_, """", """"))), ","), type text)
in
    #"Added Custom"

Pat

[Anonymous]

Hi @mahoneypat Thanks for your reply. But i do not want just the distinct values. instead the values in a sequential manner.

e.g. please check below the input and output.

Thank you

["A", "A", "A", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14"]         
["A", "K05, "A", "S14", "S14", "S14", "S14", "S14", "K05", "S14", "S14", "S14", "S14", "S14"]         
["A", "A", "A", "S14", "A", "S14", "S14", "S45", "S14", "S14", "S14", "A", "K05", "A"]                     
["A", "A", "A", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "A"]               

Distinct Sequential Quality (Output col) - corrsponding to the above rows. (sorry it wont let me paste in the table format)
A, S14
A, K05 , A, S14, K05, S14
A,S14, A, S14, S45, S14,A,K05,A
A,S14,A

[AlexisOlson]

You can transform a list like L = {"A", "A", "A", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "A"} in to a compacted list like {"A", "S14", "A"} using the List.Accumulate to define a Compactify function as follows:

(L as list) as list =>
List.Accumulate(L, {},
    (state, current) =>
        if List.Last(state, "") = current
        then state
        else state & {current}
)

You can then use that function to define a new custom column of lists as follows:

= Table.AddColumn(#"Prior Step", "Output Col", each Compactify([Input Col]))

[Anonymous]

@AlexisOlson , also, your code gives the output as {"A", "A", "S14", "A"}  instead of {"A", "S14", "A"}  

[Jakinta · Accepted solution]

Here it is with @AlexisOlson 's List.Accumulate version.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wio5RcoxR0lFAp4INTWjJiFWAAaVYHWRXeBuYkusKoFZquQJHWOB2lQlhqx3RXOmIbD8yIM4tVGdgcY9SbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quality = _t]),
    Split = Table.AddColumn(Source, "Custom", each Text.Split(Text.Select([Quality],{"0".."9","A".."Z",","}), ",")),
    Accumulate = Table.AddColumn(Split, "Sequential", each List.Accumulate ( [Custom], {}, (s,c)=> if List.Last(s&{})=c then s else s&{c} )),
    Removed = Table.SelectColumns(Accumulate,{"Sequential"}),
    Extracted = Table.TransformColumns(Removed, {"Sequential", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    Extracted

[Anonymous]

@AlexisOlson Thanks for reply. However i get an error. Could you please help rectify that?

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Also, all my columns are in Text Data Type and the input looks a bit similar as below.

Thanks for help again.

ID   Quality (Input col)                                                                                                                       
1    ["A", "A", "A", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14"]         
2    ["A", "K05, "A", "S14", "S14", "S14", "S14", "S14", "K05", "S14", "S14", "S14", "S14", "S14"]         
3    ["A", "A", "A", "S14", "A", "S14", "S14", "S45", "S14", "S14", "S14", "A", "K05", "A"]                     
4    ["A", "A", "A", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "S14", "A"]               

ID   Distinct Sequential Quality (Output col) - corrsponding to the above rows. (sorry it wont let me paste in the table format)
1    A, S14
2    A, K05 , A, S14, K05, S14
3    A,S14, A, S14, S45, S14,A,K05,A
4    A,S14,A

[AlexisOlson · Accepted solution]

If it's text, then you need to convert to a list and then back to text by using Text.Combine(Compactify(Text.Split(...)),",").

Try this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wio5RcoxR0lFAp4INTWjJiFWAAaVYHWRXeBuYkusKoFZquQJHWOB2lQlhqx3RXOmIbD8yIM4tVGdgcY9SbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quality = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Distinct Sequential Quality", each Text.Combine(Compactify(Text.Split(Text.Remove([Quality], {"[","]",""""," "}),",")),","), type text)
in
    #"Added Custom"