Forum Discussion

Z_PowerBi's avatar
Z_PowerBi
New Member
3 years ago

Help!! Creating a column with the "running" count

Hello everyone, 

 

I was wondering if anyone can help. I have the following column: 

 

 

And I want to add a further column with an ID but I need the ID to be as follows:

 

Board                                       ID

IGB                                            1

EDC                                          1

SDCB                                        1

SDCB                                         2

IGB                                            2

IGB                                            3

 

Will appreciate any help, thank you

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Assuming that you are trying to form group , please try usng Group option OOTB from Power BI . If you are looking for specific scenario , can you share some relationship on the type of relation you are seeking .

     

     

     

    Regards,

    Jupsimar Singh.

    Mark my post as a solution if it helped you !

  • Hello, Z_PowerBi try this approach

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nR3UorViVZydXEG08Euzk6oDJgKMB0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Board = _t]),
        lst = List.Buffer(Source[Board]),
        count = List.Count(lst),
        id_column =  
            List.Generate(
                () => [i = 1, board = lst{0}, value = 1],
                (x) => x[i] <= count,
                (x) => [i = x[i] + 1, board = lst{x[i]}, value = if x[board] <> lst{x[i]} then x[value] else x[value] + 1],
                (x) => {x[board], x[value]}
            ),
        to_tbl = Table.FromRows(id_column, {"Board", "ID"})
    in
        to_tbl