Convert each value of column B into column headers, grouped by column A

[freelensia]

I have a table Bounced Emails as follow:

Email         Bounce_Type
a@gmail.com   hard
a@gmail.com   soft
b@gmail.com   hard
c@gmail.com   soft

 As you can see, this table has no unique ID column! E-mails may repeat.

I want to sum over the first 2 columns like this.

Email         Hard count   Soft Count
a@gmail.com   1            1
b@gmail.com   1            0
c@gmail.com   0            1

I tried group by options but it did not yield good results.

Could somebody help me?

Thanks!

Freelensia

[freelensia · Accepted solution]

I ended up doing this:

Let's say you have a table like this:

Site Type

Đặt hẹn với phiên dịch viên trực tuyến hard

オンラインで通訳を予約する hard

オンラインで通訳を予約する soft

통역사 온라인 예약 hard

在线预订翻译员 soft

You want something equivalent to dragging the Type variable from row to column in Excel's pivot table.

How to do it

1. Do a group by Site and Type

You will get a second table like this:

Site Type Count

Đặt hẹn với phiên dịch viên trực tuyến hard 1

オンラインで通訳を予約する hard 1

オンラインで通訳を予約する soft 1

통역사 온라인 예약 hard 1

在线预订翻译员 soft 1

2. Select the Type column, right-click, choose [Pivot Column].

In the next window, choose to Aggregate [Count] column created in previous step. expand [Advanced Options] and choose [Sum]

You will get the table you want:

Site Hard Soft

Đặt hẹn với phiên dịch viên trực tuyến 1 0

オンラインで通訳を予約する hard 1 1

통역사 온라인 예약 hard 1 0

在线预订翻译员 soft 0 1

[Ashish_Mathur]

Hi,

Create a matrix visual.  Drag Email to the row labels, Boune_type to the column labels and write this measure

=COUNTROWS(Data)

Hope this helps.

[Anonymous]

I think you could do that with 2 measures.   1 for each (hard, soft), the idea would then be to setup an IF statement to populate the field if it is hard or soft.

So a calculation you could do would be 

ishard = If(Table1[BounceType] = "hard", 1, null)

issoft = If(Table1[BounceType] = "soft", 1, null)

 last step would just be to count the values 

[freelensia]

Thanks for the answer!

Sorry but I am completely new to PQ. Could you provide a bit more details on each step?