Forum Discussion

greta's avatar
greta
Frequent Visitor
2 years ago
Solved

List.Accumulate or what?

Dear All, thank you for your time.  I have a request that seems really complicated to me, I don't know if it's achievable. I've recreeated the data I'm working with in a simplified table that I'm po...
greta's avatar
greta
Frequent Visitor
2 years ago

I've adapted the query to the following dataset,

As follows:

let
    Source = Lista_OnHold,
    z = Table.Group(
        Source, {"MercatusCode"},
        {{
            "On_Hold",
            each List.PositionOf(
                Table.Sort(_, {"Index.1", Order.Descending})[ProjectStatusInternal.1],
                "On Hold",
                Occurrence.First,
                (x, y) => x <> y
            )
        }}
    )
in
    z
And I'm getting this result

Could you help me in understanding why?
Many thanks in advance

AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
2 years ago

greta I did not consider the case when all values are On Hold. Try this instead:

let
    Source = Lista_OnHold,
    z = Table.Group(
        Source, {"MercatusCode"},
        {{
            "On_Hold",
            each 
                [lst = Table.Sort(_, {"Index.1", Order.Descending})[ProjectStatusInternal.1],
                pozz = List.PositionOf(lst, "On Hold", Occurrence.First, (x, y) => x <> y),
                zz = if pozz = -1 then List.Count(lst) else pozz][zz]
        }}
    )
in
    z
  • greta's avatar
    greta
    Frequent Visitor
    2 years ago

    Works perfectly, many thanks!