add index every n th row in PQ

[henry24007]

I have a table look like this

department	col1	col2	year	index
a	1	2	2021	1
b	3	4	2021	2
a	5	6	2022	3
b	7	8	2022	4

What I want to do is 

department	col1	col2	year	index
a	1	2	2021	1
b	3	4	2021	2
a	5	6	2022	1
b	7	8	2022	2

how can I turn the index to what I want

Thanks

[Hariharan_R]

Hi @henry24007 

You can use your year column with group by.

Ref - https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

Thanks

Hari

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

My Blog :: YouTube Channel :: My Linkedin

[Jihwan_Kim]

Hi,

I am not sure whether I understood your question correctly, but please check the below and the attached pbix file.

All steps are described in Power Query Editor in the attached file.

Advanced editor in PQ.

let
    Source = Source,
    #"Grouped Rows" = Table.Group(Source, {"year"}, {{"Table", each Table.AddIndexColumn( _, "Index", 1, 1)}}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Grouped Rows", "Table", {"department", "col1", "col2", "Index"}, {"department", "col1", "col2", "Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table",{{"year", Int64.Type}, {"department", type text}, {"col1", Int64.Type}, {"col2", Int64.Type}, {"Index", Int64.Type}})
in
    #"Changed Type"

[amitchandak · Accepted solution]

@henry24007 , refer the solution by curbal

https://www.youtube.com/watch?v=7CqXdSEN2k4