Forum Discussion
Solved
Dynamically renaming values
I have a list of information, that is constantly updated, that needs values in a column to be changed to represent a different, more generic value. I would need to change each unique value in ...
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"
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"