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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mstone3
Helper III
Helper III

Indexing with a custom increment

Hello, I'm trying to find an M formula that with increase the increment by 1 after every 3 rows.  For example:

 

Row #     Index

1             1

2             1

3             1

4             2

5             2

6             2

7             3

8             3

9             3

 

Thanks very much for any suggestions!

2 ACCEPTED SOLUTIONS
v-jingzhang
Community Support
Community Support

Hi @mstone3 

 

You can achieve this by the GUI without any custom formula. Follow below steps!

 

First add an Index column starting from 1.

083004.jpg

 

Then divide Index column by 3. From Transform tab > (Number Column) Standard > Divide.

083005.jpg

 

Finally round up Index column. Transform > (Number Column) Rounding > Round Up.

083006.jpg

 

Anyway, if you want a single M formula to get the same result, you can combine above three steps into one step. Paste below code into the formula bar and modify previous step name.

= Table.TransformColumns(Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), {{"Index", each Number.RoundUp(_ / 3), type number}})

083007.jpg

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

Thanks very much - this is a great solution!

View solution in original post

4 REPLIES 4
v-jingzhang
Community Support
Community Support

Hi @mstone3 

 

You can achieve this by the GUI without any custom formula. Follow below steps!

 

First add an Index column starting from 1.

083004.jpg

 

Then divide Index column by 3. From Transform tab > (Number Column) Standard > Divide.

083005.jpg

 

Finally round up Index column. Transform > (Number Column) Rounding > Round Up.

083006.jpg

 

Anyway, if you want a single M formula to get the same result, you can combine above three steps into one step. Paste below code into the formula bar and modify previous step name.

= Table.TransformColumns(Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), {{"Index", each Number.RoundUp(_ / 3), type number}})

083007.jpg

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

Thanks very much - this is a great solution!

Anonymous
Not applicable

This would work. If your prior step is named LastStep, add a new step. In the formula bar, type:

 

= Table.AddColumn(LastStep, "NewIndex", each if Number.Mod([Index], 3) = 0 then [Index]/3 else null)

 

Then you can use Fill Up function in the GUI for the missing values.

 

--Nate

 

Thank you very much!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors
Top Kudoed Authors