Power Query - Counting Distinct entries by group

[davey4444]

Hello, as per the thread title, I am trying to produce a report which shows how many journeys per day our vans are doing from data built up from one row per individual journey.
I've been able to get a pivot table to perform this calculation by using Distinct Count of van registrations plus then the number of journeys. I'm now trying to get Power Query to perform this calculation but am struggling with the distinct count element.
My data is arranged like the table below -

Country	Registration	Van Type	Date	Valid Journey?
England	ABC123	Small	1/5/20	Y
England	ABC123	Small	1/5/20	Y
Scotland	DEF123	Small	1/5/20	Y
England	ABC123	Small	2/5/20	Y
England	ZYX123	Large	2/5/20	Y
England	DFG991	Large	2/5/20	Y
England	DFG991	Large	2/5/20	Y
Scotland	DEF123	Small	2/5/20	Y
Scotland	DEF123	Small	1/5/20	N

With my final output I'd like to show -

Country	Van Type	Date	Number of distinct vans	Number of valid journeys
England	Small	1/5/20	1	2
Scotland	Small	1/5/20	1	1
England	Small	2/5/20	1	1
England	Large	2/5/20	2	3
Scotland	Small	2/5/20	1	1

When I've tried the Group By function in Power Query I am only able to show the total number of entries (so no "Valid Journey" filtering) and I'm not at all able to get a distinct calculation of vans. Power Query is very new to me and I can see the potential for what I want to do with it but just cannot get it to work as I want at the moment.

Thanks for any help.

 

[sandy666]

you can try Pivot Table

but how you counted this?

 

[macfuller]

Are you trying to use Power Query (solution in M) or Power Pivot (solution in DAX)? I will assume DAX, though first you'll want to use PowerQuery to load the data into the data model.

You want to count the number of journeys by registration it seems, assuming that a registration always belongs to the same van and a van is always the same size.

So load the table into the data model and call it Journeys. You'll want two measures in your table. The number of journeys will simply be:

[Van Journey Count]:=CALCULATE (
    COUNTROWS (Journeys),
    Journeys[Valid Journey?] = "Y"
)

The count of van type is a bit trickier because you want the count of registrations within the van type. So this works, at least on the small sample of data you provided:

[Van Type Count]:=CALCULATE (
    COUNTROWS (
        SUMMARIZE (
            Journeys,
            Journeys[Van Type], Journeys[Registration]
        )
    ),
    Journeys[Valid Journey?] = "Y"
)

That summarizes by van type and registration

Book2
	J	K	L	M	N
28	Date	Country	Van Type	Van Type Count	Van Journey Count
29	1/5/2020	England	Small	1	2
30	1/5/2020	Scotland	Small	1	1
31	2/5/2020	England	Large	2	3
32	2/5/2020	England	Small	1	1
33	2/5/2020	Scotland	Small	1	1
Sheet1

 

[sandy666]

Expected result with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Custom = Table.AddColumn(Type, "Custom", each 1),
    Group = Table.Group(Custom, {"Country", "Van Type", "Date", "Valid Journey?"}, {{"Journal", each List.Sum([Custom]), type number}, {"Van", each Table.RowCount(Table.Distinct(_)), type number}}),
    Filter = Table.SelectRows(Group, each ([#"Valid Journey?"] = "Y")),
    TSC = Table.SelectColumns(Filter,{"Country", "Van Type", "Date", "Van", "Journal"})
in
    TSC