Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Feb 07, 2025
Solved

Missing slots calculation

Dear Experts,                      I have data like below:- Each Frame(Column A) has 4 corresponding slots(Column B) :- So, A6(955) has entry 3,8,13,18 , which is a complete set, and so on as ...
BI & Data Analysis
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 08, 2025

    I'd suggest to add helper column to group frames, something like

    =N($I1) + --([@Frame] <> $A1)

    Next we could GROUPBY by them

    =LET(
    slots, {3,8,13,18},
    frames, GROUPBY(
        HSTACK( Table1[[#All],[Group]],Table1[[#All],[Frame]] ),
        VSTACK("Missed", Table1[Slot] ),
        LAMBDA(a, ARRAYTOTEXT( TOROW( slots / ISNA( XMATCH( slots,a ) ),2 ) ) ),
        3  ),
    dropped, DROP(frames,,1),
    FILTER( dropped, NOT( ISERROR( CHOOSECOLS(dropped,2) ) ) )
)

    Performance is not very good. Perhaps we may optimize above, but don't thing that dramatically.

    By the way, in Power Query version is the bug, very last frame is not calculated correctly. Since you prefer formula I didn't fix it.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 08, 2025

With Power Query that could be like

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AllSlots = {3,8,13,18},
    SelectColumns = Table.SelectColumns(Source,{"Frame", "Slot"}),

    Frames = List.Buffer( SelectColumns[Frame] ),
    n = List.Count(Frames),
    b = List.Generate(
        () => [group = 1, i=1],
        each [i] < n,
        each [ group =
            if Frames{[i]} <> Frames{[i]-1}
            then [group]+1
            else [group], i = [i]+1 ],
        each [group]
       ),
    AddGroups = Table.FromColumns( Table.ToColumns( SelectColumns ) & {b}, 
            Table.ColumnNames(SelectColumns) & {"Group"} 
     ),
    #"Grouped Rows" = Table.Group(
        AddGroups, {"Group"},
        {{"Data", each _, type table [Frame=number, Slot=number, Group=number]}}),
    AddFrame = Table.AddColumn(
        #"Grouped Rows",
        "Frame",
        each [Data][Frame]{0}),
    AddSlots = Table.AddColumn(
        AddFrame,
        "Slots",
        each [Data][Slot]),
    AddDifference = Table.AddColumn(
        AddSlots,
        "Missed",
        each List.Difference( AllSlots, [Slots] )),
    RemoveOtherColumns = Table.SelectColumns(
        AddDifference,{"Frame", "Missed"}),
    GetMissedSlots = Table.TransformColumns(
        RemoveOtherColumns,
        {"Missed", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    FilterNoMissed = Table.SelectRows(
        GetMissedSlots,
        each ([Missed] <> "")),
    SplitSlots = Table.SplitColumn(
        FilterNoMissed,
        "Missed",
        Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
        {"Missed.1", "Missed.2", "Missed.3"})
in
    SplitSlots

Please check attached.

  • anupambit1797's avatar
    anupambit1797
    Iron Contributor
    Feb 08, 2025

    Thanks SergeiBaklan and if I want to also transform this data in below format, 

    Then how to achieve it?, I tried something in K2, but unfortunately it took slots(from column B) for all Frames == 953, in actual it should be like in O and so on for other Frame.

     

    Thanks in Advance,

    Br,

    Anupam

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 08, 2025

      Do you mean something like this?

      If so it's in second query in attached file.

Resources