#ExcelPedroWave

In the second solution to the problem of being able to insert multiple slicers in a table, we just use Power Query to solve it, being the first query equal to that of the first proposed solution: unpivoting other columns.

You can read how to unpivot the original table in this link:

Power Query Table to set up multiple slicers

Second solution

The second query with Power Query is based on the following article:

Dingbat Data - Non-aggregate pivot with multiple rows in Power Query

Cameron Wallace published in that post a generic function that solves the case there are multiple detail values for the same data type and the same person.

The generic function in Power Query looks like this:

As source, we start from the unpivoted table (TableDetails) and pass two arguments: the column to pivot (Type) and the column with the values (Details).

To understand it, TableDetails is a normalized table with 3 columns that cannot be pivoted in the usual way, as it contains multiple values of detail for the same name and type, so it will be necessary to create additional rows when this table is pivoted:

For example, Mary has lived in two different locations: London and Joburg, so it takes 2 rows to pivot that data.

For this, the Pivot Duplicates Function has been defined with this Power Query M code:

(Source as table ,ColToPivot as text ,ColForValues as text)=> let PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))) ,#"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _) ,TableFromRecordOfLists = (rec as record, fieldnames as list) => let PartialRecord = Record.SelectFields(rec,fieldnames) ,RecordToList = Record.ToList(PartialRecord) ,Table = Table.FromColumns(RecordToList,fieldnames) in Table ,#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)) ,#"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames) ,#"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames) in #"Expanded Values"

I am not going to explain these M formulas in detail because they are not mine and are explained in the blog where this function was originally published (link here). In a next post I'll publish the fourth proposed solution, where I'll explain some of these formulas.

The way to call this function is:

= #"Pivot Duplicates Function"(TableDetails, "Type", "Details")

With which the TableDetails with its Details is pivoted by the Type, resulting in the following table:

For each Name of a person, one or more rows are obtained with all their Locations, Ages & Skills.

Null values indicate that this value is the one in the previous row, so the next step is to fill those values down with this function:

= Table.FillDown(Source,{"Locations", "Ages", "Skills"})

With which the desired second solution is achieved:

Now multiple slicers can be inserted into this table, as requested in the initial forum query.

Second solution download

• From this link to Microsoft OneDrive:

Multiple Slicers PW2.xlsx

• From this link to Sites Google Drive:

Multiple Slicers PW2.xlsx

I've posted the third solution with a simplified version of the generic function that has been used in this second solution. Access it at the following link:

Power Query - multiple slicers (3/4)