Nov 24 2021 12:39 AM
There are 2 sheets with each 4 columns.
Type: Startsession or Presentation
Method: Method A, B or C
Number: Unique identification number which can occur multiple times
Region: Region can occur multiple times.
On a third sheet I have a table with the regions and I want to count how much Unique numbers had both a startsession and presentation for methodB.
Example, if the screenshot is the source file. The new table must show:
Noord holland 1
Overijssel 1
Gelderland 0
Noord brabant 0
...
...
Nov 24 2021 01:22 AM
Nov 24 2021 02:36 AM
Nov 24 2021 02:40 AM
@Pieter23 Let me start by thanking @arnel_gp for taking the time to create a workbook that replicates your data. It enabled me to, fairly easily, create the desired output with Power Query as you can see in the attached file. It may need to be optimized to fit your real life data, but it demonstrates what you can achieve without complex formulae.
If you are new to PQ, check out the link below. It's a good starting point to learn more about it.
Nov 24 2021 03:07 AM
Nov 25 2021 05:19 AM
Nov 25 2021 11:56 AM
As variant
= LET(
u, SORT( UNIQUE( Table1[Region]) ),
cnt, --( u = TRANSPOSE( Table1[Region] ) ),
pr, --( Table1[Method] = "methodB" ) * ( Table1[Type] = "Presentation" ),
ss, --( Table1[Method] = "methodB" ) * ( Table1[Type] = "StartSession" ),
CHOOSE( {1,2},
u,
MMULT( cnt, pr ) * MMULT( cnt, ss) )
)
which gives
Nov 29 2021 11:04 AM
Nov 29 2021 11:28 AM
Like this ?
= LET(
u, SORT( UNIQUE( Table1[Region]) ),
cnt, --( u = TRANSPOSE( Table1[Region] ) ),
pr, --( Table1[Method] = "methodB" ) * ( Table1[Type] = "Presentation" ),
ss, --( Table1[Method] = "methodB" ) * ( Table1[Type] = "StartSession" ),
CHOOSE( {1,2},
u,
MMULT( cnt, pr + ss ) )
)