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 YEngland Small 01/05/2020 1 2England ABC123 Small 01/05/2020 YScotland Small 01/05/2020 1 1Scotland DEF123 Small 01/05/2020 YEngland Small 02/05/2020 1 1England ABC123 Small 02/05/2020 YEngland Large 02/05/2020 2 3England ZYX123 Large 02/05/2020 YScotland Small 02/05/2020 1 1England DFG991 Large 02/05/2020 YEngland DFG991 Large 02/05/2020 YScotland DEF123 Small 02/05/2020 YScotland 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