COUNTIF in Query Editor

[AntonG]

Good Day,

What simplest formula is the equivalent for Excel Countif(s) functions in Power BI Desktop - QueryEditor - M language ? 

I would like to add "Custom Column" to the larger table and specify column formula. I tried proposed solution using Transform - Group By, but the solution is not feasible for larger tables with multiple columns because Table.Group would finally drop unused columns and transform the table to the specified columns only.

Thank you

Anton

[MFelix · Accepted solution]

Hi @AntonG ,

You need to use the group column option, you will then get the result you refer of only one colum and then you should do a merge with the previous steps to have that addtional column on your data table:

Check M code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MDAAUlFKsToQAVMwPxLMdwKyjMH8CDjfBMwPh/PNwPwwMN8ZyDIH80OVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Cat = _t, Value = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cat", type text}, {"Value", Int64.Type}, {"Type", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Cat"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Cat"},#"Grouped Rows",{"Cat"},"Grouped Rows",JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Count"})
in
    #"Expanded Grouped Rows"

Sorry for making the question but just want to help immprove your results and model.

Why are you adding this additional column to your model?

This will make you model bigger and if the numbers are used in the visuals due to duplication of values you can have wrong information.

Regards,

MFelix

[AntonG]

Hi MFelix,

appreciate your explanation and comments, thank you for asking detailing question, I have next data quiz to solve with CountIf. Basically I have repeatable invoice value coming in reports from one of the suppliers, and because other suppliers are reporting Line value and I'm appending data into a bigger table, I think I have no other choice other than do data manipulation as shown below

1	A	B	C	D
2	INVOICENO	INVOICEVALUE	INVOICENC	LINEVALUE
3			=COUNTIF(A:A,A3)	=B3/C3
4	55538778	19604.4	2	9802.2
5	55538778	19604.4	2	9802.2
6	55554866	1368	3	456
7	55554866	1368	3	456
8	55554866	1368	3	456

Anton