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.
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 column A to the generic value such as "Person 1" "Person 2" "Person 3" etc. This list gets updated with new values each week and thus the column would need to be updated dynamically. (hopefully)
I have tried the SWITCH function however the data is growing and can contain thousands of unique values which would be too time consuming to use.
Is there another way to achieve the expected results?
Example:
Data | Expected Outcome | Score |
John | Person 1 | 48 |
Bill | Person 2 | 16 |
Doug | Person 3 | 26 |
Derek | Person 4 | 13 |
Carl | Person 5 | 14 |
John | Person 1 | 2 |
John | Person 1 | 6 |
Carl | Person 5 | 16 |
Doug | Person 3 | 26 |
Bill | Person 2 | 22 |
Derek | Person 4 | 16 |
Doug | Person 3 | 14 |
Bill | Person 2 | 16 |
Derek | Person 4 | 19 |
John | Person 1 | 21 |
Carl | Person 5 | 36 |
Derek | Person 4 | 38 |
Bill | Person 2 | 48 |
Thank you for any help.
Solved! Go to Solution.
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"
A suggestion would be in the Query Editor to add an Index column to the table containing the names, then a calculated column that is "Person" + "Index". This should give you what you need every time you refresh.
(This is select Index column, then Add Column -> Column from Examples -> From Selection. Type "Person1" and hit enter, it will create the code)
Adding in a new person, auto-increments without changing the others
Hope this helps
David
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"
This should do it
Please see the attached file's query editor to follow 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]), #"Removed Columns" = Table.RemoveColumns(Source,{"Expected Outcome", "Score"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns"), #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1), #"Merged Queries" = Table.NestedJoin(Source,{"Data"},#"Added Index",{"Data"},"Added Index",JoinKind.LeftOuter), #"Expanded Added Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Index", {"Index"}, {"Index"}), #"Added Prefix" = Table.TransformColumns(#"Expanded Added Index", {{"Index", each "Person " & Text.From(_, "en-US"), type text}}), #"Renamed Columns" = Table.RenameColumns(#"Added Prefix",{{"Index", "Person"}}) in #"Renamed Columns"
Hi Zubair,
Thank you. This seems to do the trick in practice. However, I am stumbling at the merge queries step. When i attempt the merge the source data with the new index table it is not an option available in power bi. Did you duplicate tables to allow the merge of the original query and the new table with the indexed table?
Thank you
This is one cool Power Query Trick 🙂
Basically you can do a merge with one of the previous steps in the same query
Unfortunately you cannot do the merge directly from the UserInterface.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |