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
vgeldbr
Helper IV
Helper IV

Generating a list of numbers starting from specified value and restarting after reaching 12

I'm trying to address a challenge most of which is solved by @BA_Pete here: Adding columns or rows for missing data - Microsoft Power BI Community

 

I think I have an approach but I need to generate a list of month numbers that starts at a value specified in the [Start Fiscal Month] column, increments up to 12, and then starts at 1 again until the total number of iterations reaches the number of months specified.

Start Fiscal MonthStart Fiscal YearEnd Fiscal MonthEnd Fiscal YearMonth List
42023320244
42023320245
42023320246
42023320247
42023320248
42023320249
420233202410
420233202411
420233202412
42023320241
42023320242
42023320243

 

I've looked at List.Generate and List.Repeat but not been able to get to what I need.

1 ACCEPTED SOLUTION

 

--UPDATED to handle multi-year amortisation--

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jci7DcAgDIThXVxH6PyCeIZ0aRHy/ltgkuJ0n/45yQAMg0Y6F817Hwnc+bx0kVpEYylZTSB64k8/9+Xg5kFrbQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Amount in local currency" = _t, #"Start Fiscal Month" = _t, #"Start Fiscal Year" = _t, #"End Fiscal Month" = _t, #"End Fiscal Year" = _t, #"Amortization Period in months" = _t, #"Per Period Amortization Amount" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Amount in local currency", type number}, {"Start Fiscal Month", Int64.Type}, {"Start Fiscal Year", Int64.Type}, {"End Fiscal Month", Int64.Type}, {"End Fiscal Year", Int64.Type}, {"Amortization Period in months", Int64.Type}, {"Per Period Amortization Amount", type number}}),
    
    addRowNumber = Table.AddColumn(chgTypes, "rowNumber", each {0..[Amortization Period in months] - 1}),
    expandRowNumber = Table.ExpandListColumn(addRowNumber, "rowNumber"),
    addPeriod =
        Table.AddColumn(
            expandRowNumber,
            "period",
            each let
                year = Text.From([Start Fiscal Year] + (Number.RoundUp(([Start Fiscal Month] + [rowNumber]) / 12) - 1)),
                month = Text.PadStart(Text.From(Number.Mod([Start Fiscal Month] + [rowNumber] - 1, 12) + 1), 2, "0")
            in
                Number.From(Text.Combine({year, month}))),
        
    remOthCols = Table.SelectColumns(addPeriod,{"Unique ID", "Start Fiscal Month", "End Fiscal Month", "Per Period Amortization Amount", "period"})
in
    remOthCols

 

Output:

BA_Pete_0-1680185922663.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

10 REPLIES 10
ronrsnfld
Super User
Super User

Given the first four columns of your posted table (actually just need the first two columns, my approach would be to generate an appropriate List of the fiscal month numbers, and then append that to your original table.

 

The basic formula to generate that list would be: List.Transform(List.Numbers(Source[Start Fiscal Month]{0},12),
each Number.Mod(_ -1,12) + 1)

 

In code, with the table stored in Excel

let

//Change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],

//Create list of Fiscal months depending on the Start Fiscal Month column
    #"Fiscal Months" = List.Transform(List.Numbers(Source[Start Fiscal Month]{0},12), 
        each Number.Mod(_ -1,12) + 1),

//add that to the original table
    #"Add Fiscal Months Column" = Table.FromColumns(
        Table.ToColumns(Source) & {#"Fiscal Months"},
        type table[Start Fiscal Month=Int64.Type, Start Fiscal Year=Int64.Type, End Fiscal Month=Int64.Type, End Fiscal Year=Int64.Type,Month List=Int64.Type]
    )
in
    #"Add Fiscal Months Column"

 

ronrsnfld_0-1680182868779.png

 

 

AlienSx
Super User
Super User

Hello, @vgeldbr I'd also play with List.Sort( {1..12} ) and it's optional comparisonCriteria parameter as an alternative solution...

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHSUTIyMDIGUsYQpolSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Fiscal Month" = _t, #"Start Fiscal Year" = _t, #"End Fiscal Month" = _t, #"End Fiscal Year" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Fiscal Month", Int64.Type}}),
    result = 
        Table.AddColumn(
            #"Changed Type",
            "Month List",
            (x) =>
                let 
                    m_list = List.Buffer({1..12})
                in List.LastN(m_list, 13 - x[Start Fiscal Month]) & List.FirstN(m_list, x[Start Fiscal Month] - 1)
        ),
    expand_m = Table.ExpandListColumn(result, "Month List")
in
    expand_m

 

BA_Pete
Super User
Super User

 

This is the approach I was looking at to resolve your other post. Clearly I wasn't quick enough.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thanks @BA_Pete . I did not want to burden you in the original question on the finer details as your approach is what will work and your solution would work for anybody not constrained by a wierd 544 financial year model. Any guidance welcome here or there!

 

 

No problem.

 

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwMDA3MTC2jDc1BDJNTM3MzOMNDCzivYOUdJSMTSwt9QyNgCwTIDYyMDIGCUKYIBGwlJGloZ6ppVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Amount in local currency" = _t, #"Start Fiscal Month" = _t, #"Start Fiscal Year" = _t, #"End Fiscal Month" = _t, #"End Fiscal Year" = _t, #"Amortization Period in months" = _t, #"Per Period Amortization Amount" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Amount in local currency", type number}, {"Start Fiscal Month", Int64.Type}, {"Start Fiscal Year", Int64.Type}, {"End Fiscal Month", Int64.Type}, {"End Fiscal Year", Int64.Type}, {"Amortization Period in months", Int64.Type}, {"Per Period Amortization Amount", type number}}),
    
    addRowNumber = Table.AddColumn(chgTypes, "rowNumber", each {0..[Amortization Period in months] - 1}),
    expandRowNumber = Table.ExpandListColumn(addRowNumber, "rowNumber"),
    addPeriod =
        Table.AddColumn(
            expandRowNumber,
            "period",
            each let
                year = Text.From(if [Start Fiscal Month] + [rowNumber] > 12 then [End Fiscal Year] else [Start Fiscal Year]),
                month = Text.PadStart(Text.From(Number.Mod([Start Fiscal Month] + [rowNumber] - 1, 12) + 1), 2, "0")
            in
                Number.From(Text.Combine({year, month}))
        ),
        
    remOthCols = Table.SelectColumns(addPeriod,{"Unique ID", "Start Fiscal Month", "End Fiscal Month", "Per Period Amortization Amount", "period"})
in
    remOthCols

 

To get this output:

BA_Pete_0-1680180344695.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




 

--UPDATED to handle multi-year amortisation--

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jci7DcAgDIThXVxH6PyCeIZ0aRHy/ltgkuJ0n/45yQAMg0Y6F817Hwnc+bx0kVpEYylZTSB64k8/9+Xg5kFrbQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Amount in local currency" = _t, #"Start Fiscal Month" = _t, #"Start Fiscal Year" = _t, #"End Fiscal Month" = _t, #"End Fiscal Year" = _t, #"Amortization Period in months" = _t, #"Per Period Amortization Amount" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Amount in local currency", type number}, {"Start Fiscal Month", Int64.Type}, {"Start Fiscal Year", Int64.Type}, {"End Fiscal Month", Int64.Type}, {"End Fiscal Year", Int64.Type}, {"Amortization Period in months", Int64.Type}, {"Per Period Amortization Amount", type number}}),
    
    addRowNumber = Table.AddColumn(chgTypes, "rowNumber", each {0..[Amortization Period in months] - 1}),
    expandRowNumber = Table.ExpandListColumn(addRowNumber, "rowNumber"),
    addPeriod =
        Table.AddColumn(
            expandRowNumber,
            "period",
            each let
                year = Text.From([Start Fiscal Year] + (Number.RoundUp(([Start Fiscal Month] + [rowNumber]) / 12) - 1)),
                month = Text.PadStart(Text.From(Number.Mod([Start Fiscal Month] + [rowNumber] - 1, 12) + 1), 2, "0")
            in
                Number.From(Text.Combine({year, month}))),
        
    remOthCols = Table.SelectColumns(addPeriod,{"Unique ID", "Start Fiscal Month", "End Fiscal Month", "Per Period Amortization Amount", "period"})
in
    remOthCols

 

Output:

BA_Pete_0-1680185922663.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thanks @BA_Pete , this works perfectly. Thanks everyone for the ideas all of which have helped me a learn that bit more about Power Query techniques!

Vijay_A_Verma
Super User
Super User

Insert this step where #"Changed Type" should be replaced with your previous step

 

= Table.FromColumns(Table.ToColumns(#"Changed Type") & {List.Generate(()=>[x=Table.FirstValue(#"Changed Type"),i=0], each [i]<Table.RowCount(#"Changed Type"), each [i=[i]+1, x = if [x]=12 then 1 else [x]+1], each [x])},Table.ColumnNames(#"Changed Type")&{"Month List"})

 

@Vijay_A_Verma please could you elaborate? Are you assuming the table in my example exists with those exact columns? When I insert into my real query which has other columns I get totally wierd results and I don't really understand the approach you've taken (though I'm trying).

 

ChielFaber
Super User
Super User

You could try the solution offered here:

https://community.powerbi.com/t5/Desktop/Restart-count-in-column/m-p/43546

 

Haven't tried it myself, but I think that you can input the value of column StartFiscalMonth as the startposition in the index step.

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.

Top Solution Authors
Top Kudoed Authors