#ExcelPedroWave

In the previous post we saw a generic function that allows you to pivot a table in multiple rows:

Power Query - multiple slicers (2/4)

In this post we will see a second version of that generic function, as published by Cameron Wallace on GitHub:

camwally / Power-Query / fNonAggPivotMultRows2.pq

Third solution

This solution has its origin in the normalized detail table and, applying the generic function, it becomes a pivoted table with multiple rows if there is more than one location, age or skill per person.

This case cannot be solved with dynamic tables, since they do not admit several rows with the same Name.

The M code in Power Query for the generic function that pivot the table is as follows:

Source as table ,PivotCol as text ,ValueCol as text)=> let Source = Table.Buffer(Source) //As source table is referenced 3 times ,GroupClustIndex = Table.Group(Source ,List.RemoveItems(Table.ColumnNames(Source),{ValueCol}) ,{"ColOfTables" ,each Table.AddIndexColumn(_,"idx")}) ,CombineTables = Table.Combine(GroupClustIndex[ColOfTables]) ,Pivot = Table.Pivot(CombineTables, List.Distinct(Table.Column(Source,PivotCol)), PivotCol, ValueCol) ,RemoveIndex = Table.RemoveColumns(Pivot,{"idx"}) in RemoveIndex

The way to call this function is:

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

3 arguments are passed: Source as table, PivotCol as text and ValueCol as text.

The 5 steps applied by the generic function are explained below:

1) Source = Table.Buffer(Source)

//As source table is referenced 3 times, buffers the Source table in memory, isolating it from external changes during evaluation.

2) GroupClustIndex = Table.Group(Source,

List.RemoveItems(Table.ColumnNames(Source),{ValueCol}),

{"ColOfTables",each Table.AddIndexColumn(_,"idx")})

//Groups rows in the table that have the same key with List.RemoveItems function, adding an index column called "ColOfTables".

3) CombineTables = Table.Combine(GroupClustIndex[ColOfTables])

//Combine main table with the tables in the "ColOfTables" column.

4) Pivot = Table.Pivot(CombineTables,

List.Distinct(Table.Column(Source,PivotCol)), PivotCol, ValueCol)

//Pivot Source tabla with the PivotCol adding one column for each "Type" with the ValueCol from "Details" values. 

5) RemoveIndex = Table.RemoveColumns(Pivot,{"idx"})

//Remove auxiliar index.

Third solution download

• From this link to Microsoft OneDrive:

Multiple Slicers PW3.xlsx

• From this link to Sites Google Drive:

Multiple Slicers PW3.xlsx

With the generic function, you cannot click individual steps and see how the query transforms the data, so a fourth solution is required.

You can read the following post talking about the last fourth solution, without the generic function and with only a few steps applied in Power Query:

Power Query - multiple slicers (4/4)