Consolidate values in slicer

[Anonymous]

Hi, I am looking to consolidate multiple values to appear as one value in the slicer. Please see examples and explanation below. 

Example of list in excel 

Example of slicer in power bi

I would like to display all entries that contain Alex M under one value in the slicer and do this for the other entries. So the slicer will display Alex as one value, however when clicked it will produce all data that contains Alex (as well as Alex with other names). Is this possible? 

Thank you. 

[MahyarTF]

Hi,

In my opinion, you could copy the column (name column) and then split the new column (based on the first space in the particular column)

Then delete the second new column (now you have a column including the full name and another with the first name), and could create the slicer on this new column or both columns:

[Anonymous]

Hi @MahyarTF, 

Thank you for you response. This is a good idea. The problem I have is that this will only pull the first name in the list which groups the relevant list only once. Ideally, I need the slicer to group by each first name split by a comma e.g the second line in the screenshot below includes Jake, Elena, Matt. The second slicer should appear under Jake, Elena and Matt. Please see below screenshot below.

Do you think this is possible? 

Christiana

[MahyarTF]

Hi,

Please follow the below steps in Power Query :

----------------------

let
Source = Excel.Workbook(File.Contents("C:\Users\mtalehfirouz\Downloads\Book1.xlsx"), null, true),
Sheet104_Sheet = Source{[Item="Sheet104",Kind="Sheet"]}[Data],
#"Duplicated Column" = Table.DuplicateColumn(Sheet104_Sheet, "Column1", "Column1 - Copy"),
#"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",", ",",",Replacer.ReplaceText,{"Column1 - Copy"}),
#"Removed Columns5" = Table.RemoveColumns(#"Replaced Value",{"Column1"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns5", "Column1 - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1 - Copy.1", "Column1 - Copy.2", "Column1 - Copy.3", "Column1 - Copy.4"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1 - Copy.1", type text}, {"Column1 - Copy.2", type text}, {"Column1 - Copy.3", type text}, {"Column1 - Copy.4", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Column1 - Copy.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1 - Copy.1.1", "Column1 - Copy.1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1 - Copy.1.1", type text}, {"Column1 - Copy.1.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1 - Copy.1.2"}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns", "Column1 - Copy.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1 - Copy.2.1", "Column1 - Copy.2.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1 - Copy.2.1", type text}, {"Column1 - Copy.2.2", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Column1 - Copy.2.2"}),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns1", "Column1 - Copy.3", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1 - Copy.3.1", "Column1 - Copy.3.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Column1 - Copy.3.1", type text}, {"Column1 - Copy.3.2", type text}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type3",{"Column1 - Copy.3.2"}),
#"Split Column by Delimiter4" = Table.SplitColumn(#"Removed Columns2", "Column1 - Copy.4", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1 - Copy.4.1", "Column1 - Copy.4.2"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"Column1 - Copy.4.1", type text}, {"Column1 - Copy.4.2", type text}}),
#"Removed Columns3" = Table.RemoveColumns(#"Changed Type4",{"Column1 - Copy.4.2"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns3", {}, "Attribute", "Value"),
#"Removed Columns4" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns4")
in
#"Removed Duplicates"

----------------------

This is the records in first step :

And this is the result :

appreciate to Kudos

[Anonymous]

Hi @MahyarTF, 

Thank you. This is so greatly appreciated.

Will you be able to share you pbix file so I can have a closer look? 

[MahyarTF]

Hi,

https://drive.google.com/file/d/1ZlkrdfvS-2wTqEWhj9wPdkBJgjaDbBSz/view?usp=sharing

Appreciate for Kudos

[Anonymous]

Hi @MahyarTF, 

Thank you so much. I have requested access. 

[MahyarTF]

please check it

[Anonymous]

Hi @MahyarTF, 

I had a look. My issue still persists that this will only pull the first name from the list of other names. I am afraid this will not capture all the names. Please note it is okay if the last name is included. 

See the list below as an example: 

I would like to create a slicer that displays the following list of values: 

Matt Chirgwin

Sam Mackay
Alex Meyers

Marcelle Ahamefule

Cameron Hensley

Mandy Sandhu

From the coloumn below: 

1	Matt Chirgwin
2	Sam Mackay, Marcelle Ahamefule, Alex Meyers, Cameron Hasley
3	Alex Meyers, Marcelle Ahamefule
4	Marcelle Ahamefule, Cameron Hensley
5	Matt Chirgwin
6	Matt Chirgwin, Mandy Sandhu

The point is to draw the name once into the slicer which captures every time the name appears in the column i.e Marcelle Ahamefule will capture rows 2, 3 and 4, Matt Chirgwin will capture rows 1, 5 and 6. At the same time Cameron Hensley will capture rows 2 and 4. The same concept should apply to all the values. The list I am working with is a very long one with some rows with 4 to 5 names. The steps provided earlier only works for the first name on the list - I have already played around with this concept. 

Sorry for the complexity. Hope there is another solution. 

Thank you. 

[danextian]

Hi @Anonymous ,

You can create a calculated column in DAX to determine whether a cell contains the word Alex. Sample formula is

Calc Column =
IF ( CONTAINSSTRING ( Data[column], "alex" ), "Alex", "Other" )

DAX isn't case-sensitive unless you use EXACT function.

This can be done in M as well as a custom column

if Text.Contains([Column], "alex") then
  "Alex"
else
  "Other" //case-sensitive                


if Text.Contains(Text.Lower([Column]), "alex") then
  "Alex"
else
  "Other" //not case-sensitive   

This work for the word "alex" only or any single criterion.

[Anonymous]

Hi @danextian, 

Thank you for your response. This is a great idea although I need this to work for all the names. So if it only works specifically for each name it may not be the optimal solution as new names will be added to the list in the furture. I need a solution that will automatically update and group in the slicer. 

Thank you

Christiana

[danextian]

Hi @Anonymous ,

The solutions presented are based on the initial information provided. Please provide further details as well as a sample data that we can easily manipulate.

[Anonymous]

Hi @danextian, 

Thank you. Sorry for the lack of information. I would like to create a slicer that displays the following list of values: 

Matt Chirgwin

Sam Mackay
Alex Meyers

Marcelle Ahamefule

Cameron Hensley

Mandy Sandhu

From the coloumn below: 

1	Matt Chirgwin
2	Sam Mackay
3	Alex Meyers, Marcelle Ahamefule
4	Marcelle Ahamefule, Cameron Hensley
5	Matt Chirgwin
6	Matt Chirgwin, Mandy Sandhu

The point is to draw the name once into the slicer which captures every time the name appears in the column i.e Marcelle Ahamefule will capture rows 3 and 4, Matt Chirgwin will capture rows 1, 5 and 6. At the same time Cameron Hensley will capture row 4 as with Marcelle Ahamefule. The same concept should apply to all the values.  

I hope this provides a clear scope and there is a optimal solution. 

Thank you. 

[danextian]

Hi @Anonymous 

The sample code below will create a custom column that will convert [Name] in to a list split by comma. The  items in the lists are then extracted as new rows and cleaned for any preceding and succeeding spaces. Please take note that this approach cannot be used on one side of the one to many relationship as this will create repeating values for the original name column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJNLClRcM7ILEovz8xTitWJVjICigYn5ir4JiZnJ1aChYyBQo45qRUKvqmVqUXFOkC5ouTUnJxUBceMxNzUtNKcVLA6E7CB6FI6Cs5AVlF+noJHal5xTirETFOslpuhi4LsykupVAgGkhmlSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Name = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Name2", each Text.Split([Name],","), type list),
    #"Expanded Name2" = Table.ExpandListColumn(#"Added Custom", "Name2"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Name2",{{"Name2", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Name2", Text.Trim, type text}})
in
    #"Trimmed Text"