Forum Discussion
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 : )
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 ExtractedIf 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"
8 Replies
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 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,AIf 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 , also, your code gives the output as {"A", "A", "S14", "A"} instead of {"A", "S14", "A"}
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
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
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
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.
