Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Dec 21, 2025

PivotBy or GroupBy

Dear Experts,

                       Quick question, 

I have a data like this from Column A~F, and want to make a data like in Col J~L

So, I want a summary , that for the slot0 && txnum==1, what are the unique values of numOfPrb, but in case of slot5, we have more than one unique value and it spills, could you please suggest a solution for this, I tried Filter function etc.. may be there's solution within , or if Groupby or Pivotby can help here.

 

Attached is the worksheet,

 

Br,

Anupam

BI & Data Analysis
excel
Formulas and Functions
power bi

1 Reply

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Dec 21, 2025

    PIVOTBY() should work. Try-

    =PIVOTBY(Table1[slot],Table1[txNumber],Table1[numOfPrb],LAMBDA(x,ARRAYTOTEXT(UNIQUE(x))),0,0,,0)

    And using FILTER() function.

    =IFERROR(ARRAYTOTEXT(UNIQUE(FILTER(Table1[numOfPrb],(Table1[slot]=$L3)*(Table1[txNumber]=M$2)))),"")

     

Resources