Forum Discussion
Dynamically renaming values
This is a third alternative solution. I think this is better than previous 2 approaches
Using Power Query's Grouping Feature
See Table2's Query Editor in attached file for the steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNQ0lEKSC0qzs9TMAQyTSyUYnWilZwyc3IQEkZApqEZWMIlvzQdIWEMZBpBJVKLUrMRMiYgLcZgGefEIiSzTEESJmAJLNYb4ZIww2UUAWdhesTICKd7cZkFdS/uQMFiliVOLxri8IoxTsOMcUUKNLaiSpMSM4uAfBDCKgDUmpgD48cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Data = _t, #"Expected Outcome" = _t, Score = _t]), #"Grouped Rows" = Table.Group(Source, {"Data"}, {{"All Rows", each _, type table}}), #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1), #"Added Prefix" = Table.TransformColumns(#"Added Index", {{"Index", each "Person " & Text.From(_, "en-US"), type text}}), #"Expanded All Rows" = Table.ExpandTableColumn(#"Added Prefix", "All Rows", {"Expected Outcome", "Score"}, {"Expected Outcome", "Score"}) in #"Expanded All Rows"
Thank you David. That would have been the solution if the values were truly unique. Unfortunately, my statement on unique values was inaccurate. They essentially need to be tagged as "Person 1", "Person 2", etc. but will still need to be grouped together if they are the same value.
Basically, If john took the quiz seven times then "Person 1" would be listed 7 times in the column and if Becca took the quiz 4 times then "Person 8" is listed 4 times.
Thank you for your help.
demonfc - if you split the data into a table of names, and a table of results, joining on the name, when you do visualizations you can pull the "PersonX" value from the name table and the score from the results table.
And you can do this with DAX as well with the help of a normal Index Column
See the file attached as well
Person_ = "Person " & RANKX ( VALUES ( Table1[Data] ), CALCULATE ( MIN ( [Index] ), FILTER ( Table1, [Data] = EARLIER ( [Data] ) ) ), , ASC, DENSE )This is a third alternative solution. I think this is better than previous 2 approaches
Using Power Query's Grouping Feature
See Table2's Query Editor in attached file for the steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNQ0lEKSC0qzs9TMAQyTSyUYnWilZwyc3IQEkZApqEZWMIlvzQdIWEMZBpBJVKLUrMRMiYgLcZgGefEIiSzTEESJmAJLNYb4ZIww2UUAWdhesTICKd7cZkFdS/uQMFiliVOLxri8IoxTsOMcUUKNLaiSpMSM4uAfBDCKgDUmpgD48cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Data = _t, #"Expected Outcome" = _t, Score = _t]), #"Grouped Rows" = Table.Group(Source, {"Data"}, {{"All Rows", each _, type table}}), #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1), #"Added Prefix" = Table.TransformColumns(#"Added Index", {{"Index", each "Person " & Text.From(_, "en-US"), type text}}), #"Expanded All Rows" = Table.ExpandTableColumn(#"Added Prefix", "All Rows", {"Expected Outcome", "Score"}, {"Expected Outcome", "Score"}) in #"Expanded All Rows"