Great video! Great solution!! At D10 apply UNPIVOT function: =UNPIVOT("Branch "&B4:B7,C3:E3,C4:E7) Then anywhere: =PIVOTBY(HSTACK(D10:D21,C10:C21),B10:B21,B10:B21,LAMBDA(x,"✓"),,0,,0)
UNPIVOT is a reusable custom made lambda: =LAMBDA(r,c,a,LET(w,ROWS(a),l,COLUMNS(a),s,SEQUENCE(w*l),HSTACK(INDEX(r,QUOTIENT(s-1,l)+1),INDEX(c,MOD(s-1,l)+1),TOCOL(a))))
@PowerMquery Sir, apologize for late response....Search by Using phonics...like in Arabic... Ali can be Aly... Qasim can be write as Kasim...Muhammad can be written as Mohammad or Mohammed.... So while merge the query I only get the result of extract match where Ali=Ali..result despite Aly is in the list result show only single match
Link to the file
www.linkedin.com/posts/omid-motamedisedeh-74aba166_excelchallenge-powerquerychllenge-excel-activity-7224519224495329281-Leg1?
This guy is the List guru of all , amazing trick 😮
@@avininyoutuification Thanks for your support.
Awesome solution. I loved that custom column with =1
It's amazing. Thank you Omid.
@@IvanCortinas_ES I am happy that you like it
Amazing❤
@@fahadea1 You are welcome
Amazing! Thank you!
@@mnowako you are welcome
Wow super only few steps and tick part is amazing. Thank you.
@@leosaghathan2895 You are welcome
❤❤
@@saeedhoushyari9588 welcome
Great video! Great solution!!
At D10 apply UNPIVOT function:
=UNPIVOT("Branch "&B4:B7,C3:E3,C4:E7)
Then anywhere:
=PIVOTBY(HSTACK(D10:D21,C10:C21),B10:B21,B10:B21,LAMBDA(x,"✓"),,0,,0)
UNPIVOT is a reusable custom made lambda:
=LAMBDA(r,c,a,LET(w,ROWS(a),l,COLUMNS(a),s,SEQUENCE(w*l),HSTACK(INDEX(r,QUOTIENT(s-1,l)+1),INDEX(c,MOD(s-1,l)+1),TOCOL(a))))
@@Excelambda thanks
Is it possible to create phonetic matches in power query with contain condition... Can you please guide.
What do you means phonetic match? Can you give me an example?
@PowerMquery Sir, apologize for late response....Search by Using phonics...like in Arabic... Ali can be Aly... Qasim can be write as Kasim...Muhammad can be written as Mohammad or Mohammed.... So while merge the query I only get the result of extract match where Ali=Ali..result despite Aly is in the list result show only single match