SOLVED

Power Query: COUNTIFS

%3CLINGO-SUB%20id%3D%22lingo-sub-2534566%22%20slang%3D%22de-DE%22%3EPower%20Query%3A%20COUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2534566%22%20slang%3D%22de-DE%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EI%20am%20struggling%20to%20achieve%20below%20CountIfs-Formula%20in%20PowerQuery.%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20would%20be%20highly%20welcome!%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3CP%3EJDB79%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PowerQuery_CountIfs.jpg%22%20style%3D%22width%3A%20558px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294778i97700DC38F42A378%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22PowerQuery_CountIfs.jpg%22%20alt%3D%22PowerQuery_CountIfs.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2534566%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2534717%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%3A%20COUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2534717%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1100149%22%20target%3D%22_blank%22%3E%40JDB79%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EI'm%20certainly%20not%20the%20most%20suitable%20to%20submit%20a%20suggestion%20to%20you%2C%20but%20I'll%20give%20myself%20a%20try%20%3A).%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EHere%20is%20a%20small%20%3CA%20href%3D%22https%3A%2F%2Fwww.herber.de%2Fforum%2Farchiv%2F1696to1700%2F1696221_ZaehlenWenn_Power_Query.html%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Eexample%20.%3C%2FA%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewould%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20Community%20members%20reading%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2534730%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%3A%20COUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2534730%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1100149%22%20target%3D%22_blank%22%3E%40JDB79%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESteps%20could%20be%3C%2FP%3E%0A%3CP%3E-%20Group%20table%20by%20Number%20without%20aggregation%20(All%20Rows)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20565px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294789i1738F3D982534835%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20add%20custom%20column%20to%20filter%20each%20of%20tables%20on%20%22B%22%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20352px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294790iEA8FBF0209C293EA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20add%20another%20custom%20column%20to%20count%20number%20of%20rows%20in%20each%20such%20table%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20252px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294791i89FEE17D5358D54F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E(above%203%20steps%20could%20be%20combined%20in%20one%20in%20formula%20bar)%3C%2FP%3E%0A%3CP%3E-%20in%20resulting%20table%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20673px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294792i7B57A4D9B9BEC417%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ekeep%20only%20these%20columns%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20361px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294793iCD156147B391BE9F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eexpand%20Count%20column%20and%20land%20result%20into%20the%20grid%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20496px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294794i792CD7F9CE050286%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

I am struggling to achieve below CountIfs-Formula in PowerQuery. 

Any suggestions would be highly welcome!

Thank you

JDB79

PowerQuery_CountIfs.jpg

 

 

5 Replies

@JDB79 

I'm certainly not the most suitable to submit a suggestion to you, but I'll give myself a try :).

Here is a small example .

 

would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

 

 

@JDB79 

Steps could be

- Group table by Number without aggregation (All Rows)

image.png

- add custom column to filter each of tables on "B"

image.png

- add another custom column to count number of rows in each such table

image.png

(above 3 steps could be combined in one in formula bar)

- in resulting table

image.png

keep only these columns

image.png

expand Count column and land result into the grid

image.png

Please check in attached file.

best response confirmed by JDB79 (New Contributor)
Solution

@Sergei Baklan 

Hello, 

Many thanks. Yes, this will work for me.

 

Thank you.

 

JDB79

 

Hallo,
Vielen Dank für die Mühe. Problem ist für mich gelöst.

P.S. Bin sogar auf eine weitere Lösung gestoßen:
https://stackoverflow.com/questions/53305097/countifs-equivalent-in-power-query-m-counts-per-row-wit...

Problem ist für mich gelöst.

@JDB79 , glad it helped