cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mohit139
Helper I
Helper I

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

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

2 ACCEPTED SOLUTIONS
Jakinta
Solution Specialist
Solution Specialist

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

View solution in original post

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"

 

AlexisOlson_0-1623938310063.png

 

View solution in original post

8 REPLIES 8
mohit139
Helper I
Helper I

@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
Super User IV
Super User IV

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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

 

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

Jakinta
Solution Specialist
Solution Specialist

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

View solution in original post

@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

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"

 

AlexisOlson_0-1623938310063.png

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors