PQ Countifs based on two columns

Question

Hi,&nbsp;I loaded the below table (first 4 columns) into Power Query, and&nbsp;want to add a column&nbsp;that counts the total number of occurances of the Product and Region combinations.&nbsp;I tried Group By on Product and Region, but Name and S column were removed, and I would like to&nbsp;retain them&nbsp;Hope someone can help.&nbsp;ProductRegionName$Desired Result123NorthDavid1003123NorthDavid1003123NorthEric1003456CentralEric1002456CentralPeter1002789CentralPeter1001789NorthTom10011010NorthTom1001123SouthTom1001&nbsp;Thanks,&nbsp;

alb · Accepted Answer

Hi john-paul&nbsp;
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"
&nbsp;
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&nbsp;
&nbsp;

Forum Discussion

PQ Countifs based on two columns

2 Replies

Related content

CountIf Multiple Columns

countifs based multiple columns criteria

Index Countif

Sequential Order column based on multiple criteria

Countifs

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