Forum Discussion
Creating an Excel formula
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 mu...
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.
Riny_van_Eekelen watched couple of youtube tutorials but still did'nt find out how to get the result I need. Can you help out?
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
- SergeiBaklan, thank you for your response. The only criteria missing in the formula is the unique number. Based on the unique number a region must count +1 if the number has both had a StartSession and Presentation for methodB.
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 ) ) )
