Search code examples
powerbipowerquerym

Find position number for each group (Table.PositionOf() with Table.Group) in Power Query (M language)


I'd like to find number of position first "TAK" in column "taknie" for each group (column Grupa).

This is my table.

Grupa taknie
g1 NIE
g1 TAK
g2 NIE
g2 NIE
g2 NIE
g2 TAK
g3 TAK
g3 TAK
g3 NIE
g3 TAK
g4 NIE
g4 TAK
g4 TAK
g5 NIE

I've tried in Advanced editor

let
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    Grupa = Table.Group(Source, "Grupa", {"wynik", each Table.PositionOf(
        Source, 
        [taknie = "TAK"], 
        Occurrence.First,
        "taknie"
    ) + 1})
in
    Grupa

but it doesn't work properly:

enter image description here

Instead of 2, 2, 2, 2, 2, should be

Grupa wynik
g1 2
g2 4
g3 1
g4 2
g5 0

Solution

  • enter image description here

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjdUUNJR8vN0VYrVgfFCHL0hPCMUOYI8uD5jQjy4PlQ5ExQ5E0w5OM8UrjIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Grupa = _t, taknie = _t]),
        #"Grouped Rows" = Table.Group(Source, {"Grupa"}, {{"First", each List.PositionOf  ([#"taknie"], "TAK")+1, type nullable text}})
    in
        #"Grouped Rows"