Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Nov 10, 2025

Filter Function or any Logic

Dear Experts,                      I have a data like below , Column "H" has rnti's , Column "F" has sfn which can range from 0~1023( and once 1023 it shall start again from 0) slots that can range ...
BI & Data Analysis
excel
Formulas and Functions
power bi
anupambit1797's avatar
anupambit1797
Iron Contributor
Nov 17, 2025

No OliverScheurich​ , the data also we need to calculate, attached is the sheet again, Worksheet home is the actual data,

1st Col-K we need to calculate for each rnti:-

As an example, I just created a sheet for rnti==9400, and created the data for it(Col-K and M)

then output like below can be easily be populated

which , I need for all rnti's in the home tab.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 18, 2025

    I've found your sample file in an earlier thread. However there is only one rnti in your sample file that is 384. I've attached your sample file "Filter_Function.xlsx" with suggested code.

    For my sample data there are now 2 queries as shown in the screenshot. One query returns the final output and the other query shows the intermediary step that includes the "spdu-" column. My sample file is "Filter Function or any Logic.xlsx".

     

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Nov 18, 2025

    so the file is still not attached (at least I can't see it). please share using a cloud option like onedrive or google drive.  I think I get it.  See the attached file. I have 2 output options, the first is:

    =LET(rnti,data[rnti],ack,data[clrCtrl],
same,--DROP(VSTACK(ack,-1)=VSTACK(-1,ack),-1),
counts, SCAN(0,same,LAMBDA(p,q,(p+q)*q)),
countsB, IF(counts>6,6,counts),
PIVOTBY(rnti,"spcu-"&countsB,countsB,COUNT))

    but this one has spcu-0 column and total columns and row (which I thought might be helpful)

    but this will take those things away:

    =LET(rnti,data[rnti],ack,data[clrCtrl],
same,--DROP(VSTACK(ack,-1)=VSTACK(-1,ack),-1),
counts,SCAN(0,same,LAMBDA(p,q,(p+q)*q)),
countsB,IF(counts>6,6,counts),
tabl,PIVOTBY(rnti,"spcu-"&countsB,countsB,COUNT,0,0,,0),
HSTACK(TAKE(tabl,,1),DROP(tabl,,2)))

     

    • anupambit1797's avatar
      anupambit1797
      Iron Contributor
      Nov 18, 2025

      Hi Everyone, I am trying this for the 1st time( to share a file via Google Drive)

       

      https://docs.google.com/spreadsheets/d/1nKNKhXTLyUJC32v6oegsjFUMBiKXzaCN/edit?pli=1&gid=1146938509#gid=1146938509

       

      Let me know if this is accessible.

       

      Br,

      Anupam

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Nov 19, 2025

        Very good idea. The HLINK works but we can't access the file.
        When sharing it you should grant read rights - at least

Resources