#ExcelPedroWave

A week ago I answered a question from the forum MrExcel.com asking for help creating multiple slicers for a table like this:

MrExcel.com - multiple slicers

I have a table of data with locations, ages, and skills in the left hand column. Can a slicer be set up for each data type? ie one for the locations, one for the ages, and one for the skills.

The problem is that locations, ages and skills are in the same column, so slicers cannot be created for those data types with a table that is not normalized.

If the table were normalized it would be very easy to insert slicers. For example in this table:

When the data layout is all in column A, as in the first table, we need to transform that table to get a table with a column for each type of data: locations, ages and skills.

Transformations

First of all, it is necessary to indicate what type of data is in each row of the original table, including a new column on the left for the data types, as in the following table:

With this new column, it is perfectly determined what type of data each detail corresponds to, something that humans find easy to associate because we have natural intelligence, but that machines and spreadsheets find it impossible without artificial intelligence, and we have to give them concrete ideas, so they can associate each field with data to its specific entity.

Below I explain my human logic used to try to solve this problem. In this post I am going to propose 4 possible solutions to this problem, following the flow of my reasoning, as I try more formulas in Power Query M language.

All the 4 proposed solutions go through unpivot columns thanks to the Power Query tool (link here).

First solution

I use Power Query to select the Type and Details columns and unpivot other columns with each person's data. Also I insert a new merged column: Name-Type. This is the result:

With the previous table as a data source, I have inserted a dynamic table (left) and an auxiliary table with formulas (right), which will need to be adjusted in size each time the source data is updated:

One more pivot table must be created with data source in the auxiliary table and finally the slicers are created:

First solution download

• From this link to Microsoft OneDrive:

Multiple Slicers PW1.xlsx

• From this link to Sites Google Drive:

Multiple Slicers PW1.xlsx

In this file you can analyze:

• Power Query M code

let Source = Excel.CurrentWorkbook(){[Name="TableDetails"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Details", type text}, {"Mike", type text}, {"Denis", type text}, {"Mary", type text}, {"Sally", type text}, {"Peter", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Details", "Type"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Name"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Value"}), #"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "Name-Type", each Text.Combine({[Name], [Type]}, "-"), type text) in #"Inserted Merged Column"

The main M function is Table.UnpivotOtherColumns, which allows unpivot other non-selected columns, in this case the people names as "Attribute":

Table.UnpivotOtherColumns(#"Changed Type", {"Details", "Type"}, "Attribute", "Value")

Univot data is more complicated with VBA than with Power Query. See VBA code to unpivot data here.

• Auxiliary table formulas, as in F2 cell:

=IFERROR(VLOOKUP($E2 & "-" & F$1,INDIRECT("$B" & ROW($E2) & ":$C$" & COUNTA($A:$A)),2,FALSE),F1)

• Pivot tables with slicers as the above image.

You can read the second solution to this problem here:

Power Query - multiple slicers (2/4)