Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
henry24007
Helper I
Helper I

add index every n th row in PQ

I have a table look like this

 

departmentcol1col2yearindex
a1220211
b3420212
a5620223
b7820224

What I want to do is 

departmentcol1col2yearindex
a1220211
b3420212
a5620221
b7820222

 

how can I turn the index to what I want

 

Thanks

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User
3 REPLIES 3
Hariharan_R
Solution Sage
Solution Sage

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
Super User
Super User

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"

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


amitchandak
Super User
Super User

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.