Forum Discussion
Need a Measure or Calculated Column for Visual
- 2 years ago
Awesome! That worked for me! I appreciate the help...
There may be easier ways but this is my first thought.
In Power Query, create a new table from your original data (use "Reference").
Add an index (this is only so that the group by function retains the year order).
Group by Player ID and summarise Year (use Text.Combine function - you will have to edit the Group by AFTER you have created it):
let
Source = Table,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"year", type text}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type", {"year", "player"}),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"player", Order.Ascending}, {"year", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"player"}, {{"Years", each Text.Combine([year], "; "), type nullable text}})
in
#"Grouped Rows"
Now go to Model View, and create a relationship on Player ID between your new and original tables (it may already do it - but you need to decide whether you want the relationship to be one-way or both ways).
The either add a new slicer with the new combined year variable from the new table, or add a visual-level or page-level filter.
- cmcgo32 years agoHelper II
Awesome! That worked for me! I appreciate the help...