PQ - Non standard approach to Counting Distinct entries by group

[sandy666]

Non standard approach to Counting Distinct entries by group


CountryRegistrationVan TypeDateValid Journey?CountryVan TypeDateDistinct VanValid Journeys

EnglandABC123Small
01/05/2020
YEnglandSmall
01/05/2020
1
2

EnglandABC123Small
01/05/2020
YScotlandSmall
01/05/2020
1
1

ScotlandDEF123Small
01/05/2020
YEnglandSmall
02/05/2020
1
1

EnglandABC123Small
02/05/2020
YEnglandLarge
02/05/2020
2
3

EnglandZYX123Large
02/05/2020
YScotlandSmall
02/05/2020
1
1

EnglandDFG991Large
02/05/2020
Y

EnglandDFG991Large
02/05/2020
Y

ScotlandDEF123Small
02/05/2020
Y

ScotlandDEF123Small
01/05/2020
N



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