How to create a calculated column that will count occurrences per categories?

[Byte_me]

Hi PowerBi community!
I have particual issue. Each month we generate material consumption report. For each product sometimes we have doubled material of the same category (due to ad-hoc changes our system shows that we consumed two caps for one bottle, obviously it's not possible, but it happens if we have to quickly use another cap than orginaly designed).

I would like to create a calculated coulumn that for each product ID would flag the duplications (the material ID is different, but it is the same material type). Bascially I need to count amount of occurences of string in "material type" column in group "product ID" that is limited by city and month. 

Month	City	Product ID	Material ID	Material type	Occurence
1	A	11111111	12342344	Cap	2
1	A	11111111	21321321	Bottle	1
1	A	11111111	12342344	Cap	2
1	A	11111111	24534666	Label	1
1	B	11111111	12354356	Case	2
2	B	11111111	12342344	Cap	1
2	B	11111111	24534666	Label	1
2	B	11111111	12354356	Case	2
2	A	22222222	3242355	Bottle	1
2	A	22222222	3453456	Cap	1

Kind Regards

[mahoneypat · Accepted solution]

This simplest way to do this in your query would be with a standard Group By step, where you group on Material ID, and City (and Month or other colums if needed too). You can then summarize on the count of rows for each grouping to get your desired column. Note that this would eliminate the duplicate rows, but show you the original # of replicates in the new column. If you want to keep all the original rows, just also add an "All Rows" aggregation and then expand it, as shown below.

Just create a blank query, open the Advanced Editor, and replace the code there with the below, to see how it works. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZDLCoAgEEX/xbUb5+G+2vYH4sKgXVCQ/0+jSVBN0DDCXRzuUUMwzljTyXFtSgQkWZI4pM1Eq1LgsK7Efs15mb/An3XESN57iWOa5uXi+lcbE7KvbfspBRV7ShVKleptqrS8AdpIRBAn8/1HNK5oW5vcLR4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, City = _t, #"Product ID" = _t, #"Material ID" = _t, #"Material type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"City", type text}, {"Product ID", Int64.Type}, {"Material ID", Int64.Type}, {"Material type", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"City", "Material ID"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"AllRows", each _, type table [Month=nullable number, City=nullable text, Product ID=nullable number, Material ID=nullable number, Material type=nullable text]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Month", "Product ID", "Material type"}, {"Month", "Product ID", "Material type"})
in
    #"Expanded AllRows"

Pat

[Byte_me]

Thank you so much Pat! I will build the query over next two days and come back to report results and also to accept as solution in case it will work for future help seekers. 

[Byte_me]

It works! Thank you so much! How I could turn that into PowerQuery langugage so it would become a static "flag" in my data base instead of dynamic calculation? 

[tamerj1]

Hi @Byte_me 

please use

=
COUNTROWS (
    CALCULATETABLE (
        'Table'[Material type],
        ALLEXCEPT (
            'Table',
            'Table'[Month],
            'Table'[City],
            'Table'[Product ID],
            'Table'[Material Type]
        )
    )
)