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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jasel
Helper II
Helper II

Restart count in column

I'm adding a column to a date table.  The column needs to count to 20 by rows and restart.

 

Example:

 

Year     Month     Day     BusPeriod

2013    06            13        15

2013    06            14        16

2013    06            15        17

2013    06            16        18

2013    06            17        19

2013    06            18        20

2013    06            19        1

2013    06            20        2

2013    06            21        3

2013    06            22        4

2013    06            23        5

 

 

Any suggestion on how to go about this or if this is even possible?

1 ACCEPTED SOLUTION
arify
Employee
Employee

You can add an index column, and then transform that column into something like: Number.Mod([Index] + 1, 20)

 

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Calculated Factorial" = Table.TransformColumns(#"Added Index",{{"Index", (index) => Number.Mod(index, 20) + 1, type number}})
in
    #"Calculated Factorial"

View solution in original post

2 REPLIES 2
arify
Employee
Employee

You can add an index column, and then transform that column into something like: Number.Mod([Index] + 1, 20)

 

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Calculated Factorial" = Table.TransformColumns(#"Added Index",{{"Index", (index) => Number.Mod(index, 20) + 1, type number}})
in
    #"Calculated Factorial"

Works like a charm, nice solution @arify!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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