Distilling down info from multiple rows

[daithiboy]

Hi folks,

I am wondering is there a way to summarise the table below so that it lets me know if an event had only newcomers, only returns, or returns and newcomers.

For instance, I am trying to distill this table down to:

On 11/11/19, the "Shop" had "New and Return" customers while the "Show" had only "Return" customers and the "Online" customers were all "new".

I'm not sure if this can be done with a pivot table or by using a mixture of formulas, but I've spent a couple of hours trying to figure this out!

Many thanks in advance,
Dave

Type Event Date New Shop 11/11/2019 New Shop 11/11/2019 Walk-in New Shop 11/11/2019 Walk-in New Shop 11/11/2019 Return Shop 11/11/2019 Return Shop 11/11/2019 Return Shop 11/11/2019 Return Show 11/11/2019​ Return​ Show 11/11/2019​ Return​ Show 11/11/2019​ Return​ Show 11/11/2019​ Return​ Show 11/11/2019​ Return Show 11/11/2019​ New Online 11/11/2019​ New Online 11/11/2019​ New Online 11/11/2019​ <tbody> </tbody>

<tbody>
</tbody>

 

[sandy666]

is that what you want?

Type	Event	Date		PivotTable				PowerQueryTable
New	Shop	11/11/2019​		Event	Type	Date		Event	Type	Date
New	Shop	11/11/2019​		Online	New	11/11/2019​		Online	New	11/11/2019​
Walk-in New	Shop	11/11/2019​		Shop	New	11/11/2019​		Shop	New	11/11/2019​
Walk-in New	Shop	11/11/2019​		Shop	Return	11/11/2019​		Shop	Return	11/11/2019​
Return	Shop	11/11/2019​		Shop	Walk-in New	11/11/2019​		Shop	Walk-in New	11/11/2019​
Return	Shop	11/11/2019​		Show	Return	11/11/2019​		Show	Return	11/11/2019​
Return	Shop	11/11/2019​
Return	Show	11/11/2019​
Return	Show	11/11/2019​
Return	Show	11/11/2019​
Return	Show	11/11/2019​
Return	Show	11/11/2019​
Return	Show	11/11/2019​
New	Online	11/11/2019​
New	Online	11/11/2019​
New	Online	11/11/2019​

M code for PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Event", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Type", "Event", "Date"}, {{"Count", each _, type table}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Type", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Event", "Type", "Date", "Count"})
in
    #"Reordered Columns"[/SIZE]

 

[alansidman]

Cannot get image to post correctly but suggest a pivot table with a count of events by date.