PQ Countifs based on two columns

[john-paul]

Hi,

I loaded the below table (first 4 columns) into Power Query, and want to add a column that counts the total number of occurances of the Product and Region combinations.

I tried Group By on Product and Region, but Name and S column were removed, and I would like to retain them

Hope someone can help.

Product	Region	Name	$	Desired Result
123	North	David	100	3
123	North	David	100	3
123	North	Eric	100	3
456	Central	Eric	100	2
456	Central	Peter	100	2
789	Central	Peter	100	1
789	North	Tom	100	1
1010	North	Tom	100	1
123	South	Tom	100	1

Thanks,

[AlB · Accepted solution]

Hi @john-paul 

Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJR8ssvKskA0i6JZZkpQNrQwEApVoc0WdeizGQkSRNTMyDPOTWvpCgxh7B0QGpJahGSvLmFJRHyMLtD8nOR3WVgaIBbEuzo4PxSdLlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Region = _t, Name = _t, #"$" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", Int64.Type}, {"Region", type text}, {"Name", type text}, {"$", Int64.Type}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product", "Region"}, {{"aux", each _},{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Expanded aux" = Table.ExpandTableColumn(#"Grouped Rows", "aux", {"Name", "$"}, {"Name", "$"})
in
    #"Expanded aux"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[john-paul]

Thanks AIB for your help.