CODEWITHSUNDEEP
excelpowerquery
Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

Convert rows to columns in PowerQuery. Transpose & Pivot methods not giving correct results

I have a PowerQuery table called "On Holds" in Excel as below: 

| Action User | Index |
|-------------|-------|
| Employee A  | 1     |
| Employee B  | 1     |
| Employee B  | 1     |
| Employee B  | 1     |
| Employee C  | 1     |
| Employee C  | 1     |
| Employee C  | 1     |
| Employee C  | 1     |
| Employee C  | 1     |
| Employee D  | 1     |
| Employee D  | 1     |
| Employee D  | 1     |

I'm looking to convert it to look like this (it'll have column headers, but I'm not bothered what they are): 

| Employee A | 1 |   |   |   |   |
| Employee B | 1 | 1 | 1 |   |   |
| Employee C | 1 | 1 | 1 | 1 | 1 |
| Employee D | 1 | 1 | 1 |   |   |

I have a separate table (TeamMembers) that lists unique employee names that I've tried joining to create this list but ultimately end up with versions similar to the original table.

I could do it with VBA or a series of Transpose formula outside of Powerquery but feel this isn't the best way to go - formula would need updating each week to take into account the different number of rows.

Any help would be greatly appreciated.

A basic transpose doesn't work as it ends up like this: 

| Column1    | Column2    | Column3    | Column4    | Column5    | Column6    |
|------------|------------|------------|------------|------------|------------|
| Employee A | Employee B | Employee B | Employee B | Employee C | Employee C |
| 1          | 1          | 1          | 1          | 1          | 1          |

A pivot ends up like this: 

| Employee A | Employee B | Employee C | Employee D |
|------------|------------|------------|------------|
| 1          | 3          | 5          | 3          |

Upvotes: 0

Views: 1996

Answers (3)

Sergei
Sergei

Reputation: 1

As variant

let
   Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Action User", type text}, {"Index", type text}}),
   #"Grouped Rows" = Table.Group(#"Changed Type", {"Action User"}, {{"Count", each _[Index]}}),
   Custom1 = Table.FromColumns(#"Grouped Rows"[Count],#"Grouped Rows"[Action User]),
   #"Demoted Headers" = Table.DemoteHeaders(Custom1),
   #"Transposed Table" = Table.Transpose(#"Demoted Headers")
in
   #"Transposed Table"

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40204

Another way to do this is to do a Group By and change your aggregating function. To concatenate instead of sum/max/average. Then you can split by the delimiter you use in your concatenating Text.Combine function.

When you do a Group By, the step usually looks like this:

= Table.Group(Source, {"ActionUser"}, {{"Sum", each List.Sum([Index]), type number}})

What I've done is replace List.Sum([Index]) by the following:

Text.Combine(List.Transform([Index], Text.From), ",")

This converts the numbers to text and then concatenates them with Text.Combine using a comma as the delimiter to give a table like this:

Group By Concatenated

From here, Transform > Split Column > By Delimiter will complete the job.

Full M code:

let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"ActionUser"}, {{"Concat", each Text.Combine(List.Transform([Index], Text.From), ","), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Concat", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Concat.1", "Concat.2", "Concat.3", "Concat.4", "Concat.5"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Concat.1", Int64.Type}, {"Concat.2", Int64.Type}, {"Concat.3", Int64.Type}, {"Concat.4", Int64.Type}, {"Concat.5", Int64.Type}})
in
    #"Changed Type"

Upvotes: 0

horseyride
horseyride

Reputation: 21298

The overall trick is to get a new column with cumulative count on Action User into the table:

Select Action User column. Group By ... [x] Basic, Group by:Action User, New column Name:Count, Operation:Count Rows [ok]

and modify resulting code from 

= Table.Group(Source, {"Action User"}, {{"Count", each Table.RowCount(_), type number}})

to

= Table.Group(Source, {"Action User"}, {{"Count", each Table.AddIndexColumn(_, "Cum",1,1), type table}})

Expand Index and Cum by clicking on arrows next to Count column header

Select Cum column, Pivot column.., using Index as Values Column [x] advanced options, don't aggregate

Full code below

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Action User"}, {{"Count", each Table.AddIndexColumn(_, "Cum",1,1), type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Index", "Cum"}, {"Index", "Cum"}),
#"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Count", {{"Cum", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Count", {{"Cum", type text}}, "en-US")[Cum]), "Cum", "Index")
in #"Pivoted Column1"

Upvotes: 2

Related Questions