Non standard approach to Counting Distinct entries by group
Country |
Registration |
Van Type |
Date |
Valid Journey? |
|
Country |
Van Type |
Date |
Distinct Van |
Valid Journeys |
England |
ABC123 |
Small |
01/05/2020 |
Y |
|
England |
Small |
01/05/2020 |
1 |
2 |
England |
ABC123 |
Small |
01/05/2020 |
Y |
|
Scotland |
Small |
01/05/2020 |
1 |
1 |
Scotland |
DEF123 |
Small |
01/05/2020 |
Y |
|
England |
Small |
02/05/2020 |
1 |
1 |
England |
ABC123 |
Small |
02/05/2020 |
Y |
|
England |
Large |
02/05/2020 |
2 |
3 |
England |
ZYX123 |
Large |
02/05/2020 |
Y |
|
Scotland |
Small |
02/05/2020 |
1 |
1 |
England |
DFG991 |
Large |
02/05/2020 |
Y |
|
|
|
|
|
|
England |
DFG991 |
Large |
02/05/2020 |
Y |
|
|
|
|
|
|
Scotland |
DEF123 |
Small |
02/05/2020 |
Y |
|
|
|
|
|
|
Scotland |
DEF123 |
Small |
01/05/2020 |
N |
|
|
|
|
|
|
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ones = Table.AddColumn(Table.TransformColumnTypes(Source,{{"Date", type date}}), "Ones", each 1),
Group = Table.Group(Ones, {"Country", "Van Type", "Date", "Valid Journey?"}, {{"Valid Journeys", each List.Sum([Ones]), type number}, {"Distinct Van", each Table.RowCount(Table.Distinct(_)), type number}}),
TSC = Table.SelectColumns(Table.SelectRows(Group, each ([#"Valid Journey?"] = "Y")),{"Country", "Van Type", "Date", "Distinct Van", "Valid Journeys"})
in
TSC
Bookmarks