Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 : )
Solved! Go to 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
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"
@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.
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
To learn more about Power BI, follow me on Twitter or subscribe 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
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"}
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
@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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |