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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
vgeldbr
Helper IV
Helper IV

Adding columns or rows for missing data

I have a set of data related to amortization of costs that looks like below:

Unique IDFiscal YearPosting PeriodVendorAmount in local currencyStart Fiscal MonthStart Fiscal YearEnd Fiscal MonthEnd Fiscal YearAmortization Period in monthsPer Period Amortization AmountJul'22Aug'22Sep'22Oct'22Nov'22Dec'22Jan'23Feb'23Mar'23Apr'23May'23Jun'23Current FY Amortization Months PlannedNo of Months AmortizedNo of Amortization Months OutstandingMonths greater than 12Current AmountNon Current AmountActual Prepaid Exp Balance
400074039_5100045667_008_KR202354000740393499.12420233202412291.59000291.59291.59291.59291.59291.59291.59291.59291.59291.5996601749.5801749.58

 

Unfortunately the monthly amortization amount is shown only for the months in the current fiscal year (2023). However, the amortization extends into future fiscal years (2024 in the sameple above). The months in the next financial year are shown in End Fiscal Month and End Fiscal Year. In this case month 4 for 2024. The full period of the amortization (12 months in this case) is in Period in Months. The monthly amortization amount is shown in Per Period Amortization Amount.

 

I would like to create visualizations and do other analysis that requires that the months be extended to include the future fiscal years with the correct amount entered for each of the future fiscal year months. 

 

Does anybody have any suggestions? I have tried unpivoting the data and then I thought I could do some sort of fill-down but am stuck as the number of lines to fill down by depends on how many months of amortization exist for how many future months/future years. The unpivoted data looks like this:

 

Unique IDStart Fiscal MonthStart Fiscal YearEnd Fiscal MonthEnd Fiscal YearCurrent FY Amortization Months PlannedNo of Months AmortizedNo of Amortization Months OutstandingAttributeValue
400074039_5100045667_008_KR4202332024966Jul'220
400074039_5100045667_008_KR4202332024966Aug'220
400074039_5100045667_008_KR4202332024966Sep'220
400074039_5100045667_008_KR4202332024966Oct'22291.59
400074039_5100045667_008_KR4202332024966Nov'22291.59
400074039_5100045667_008_KR4202332024966Dec'22291.59
400074039_5100045667_008_KR4202332024966Jan'23291.59
400074039_5100045667_008_KR4202332024966Feb'23291.59
400074039_5100045667_008_KR4202332024966Mar'23291.59
400074039_5100045667_008_KR4202332024966Apr'23291.59
400074039_5100045667_008_KR4202332024966May'23291.59
400074039_5100045667_008_KR4202332024966Jun'23

291.59

 

If I merge in a date table I get the extra rows but need a way to complete only those rows that have valid amortization amounts in the future years/months:

Unique IDStart Fiscal MonthStart Fiscal YearEnd Fiscal MonthEnd Fiscal YearCurrent FY Amortization Months PlannedNo of Months AmortizedNo of Amortization Months OutstandingMonth.1Month.2AmortizationMonth.1 FullFiscal Month NumberFiscal Year Month Number
400074039_5100045667_008_KR4202332024966Jul20220July1202301
400074039_5100045667_008_KR4202332024966Aug20220August2202302
400074039_5100045667_008_KR4202332024966Sep20220September3202303
400074039_5100045667_008_KR4202332024966Oct2022291.59October4202304
400074039_5100045667_008_KR4202332024966Nov2022291.59November5202305
400074039_5100045667_008_KR4202332024966Dec2022291.59December6202306
400074039_5100045667_008_KR4202332024966Jan2023291.59January7202307
400074039_5100045667_008_KR4202332024966Feb2023291.59February8202308
400074039_5100045667_008_KR4202332024966Mar2023291.59March9202309
400074039_5100045667_008_KR4202332024966Apr2023291.59April10202310
400074039_5100045667_008_KR4202332024966May2023291.59May11202311
400074039_5100045667_008_KR4202332024966Jun2023291.59June12202312
nullnullnullnullnullnullnullnullnullnullnullnullnull202401
nullnullnullnullnullnullnullnullnullnullnullnullnull202402
nullnullnullnullnullnullnullnullnullnullnullnullnull202403
nullnullnullnullnullnullnullnullnullnullnullnullnull202404
nullnullnullnullnullnullnullnullnullnullnullnullnull202405
nullnullnullnullnullnullnullnullnullnullnullnullnull202406
nullnullnullnullnullnullnullnullnullnullnullnullnull202407
nullnullnullnullnullnullnullnullnullnullnullnullnull202408
nullnullnullnullnullnullnullnullnullnullnullnullnull202409
nullnullnullnullnullnullnullnullnullnullnullnullnull202410
nullnullnullnullnullnullnullnullnullnullnullnullnull202411
nullnullnullnullnullnullnullnullnullnullnullnullnull202412
nullnullnullnullnullnullnullnullnullnullnullnullnull202501
nullnullnullnullnullnullnullnullnullnullnullnullnull202502
nullnullnullnullnullnullnullnullnullnullnullnullnull202503
nullnullnullnullnullnullnullnullnullnullnullnullnull202504
nullnullnullnullnullnullnullnullnullnullnullnullnull202505
nullnullnullnullnullnullnullnullnullnullnullnullnull202506
nullnullnullnullnullnullnullnullnullnullnullnullnull202507

 

Does anybody have any ideas or pointers?

 

1 ACCEPTED SOLUTION

 

No. Should be doable just using a slightly different year calculation.

--UPDATE-- @vgeldbr  Try this:

 

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

 

 

To get this:

BA_Pete_0-1680185679404.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

5 REPLIES 5
BA_Pete
Super User
Super User

Hi @vgeldbr ,

 

The below query turns this:

BA_Pete_0-1680097912162.png

 

...into this:

BA_Pete_1-1680097940400.png

 

 

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}}),
    addDtStartMonth = Table.AddColumn(chgTypes, "dtStartMonth", each Date.StartOfMonth(
    #date(
        [Start Fiscal Year],
        [Start Fiscal Month],
        01
    )
)),
    addDtEndMonth = Table.AddColumn(addDtStartMonth, "dtEndMonth", each Date.EndOfMonth(
    #date(
        [End Fiscal Year],
        [End Fiscal Month],
        01
    )
)),
    addMonthList =
    Table.AddColumn(
        addDtEndMonth,
        "monthList",
        each List.Distinct(
            List.Transform(
                { Number.From([dtStartMonth])..Number.From([dtEndMonth]) },
                each Date.StartOfMonth(Date.From(_))
            )
        )
    ),
    expandMonthList = Table.ExpandListColumn(addMonthList, "monthList"),
    remOthCols = Table.SelectColumns(expandMonthList,{"Unique ID", "Start Fiscal Month", "Start Fiscal Year", "End Fiscal Month", "End Fiscal Year", "Per Period Amortization Amount", "monthList"})
in
    remOthCols

 

 

It looks pretty big and complicated, but in summary, it's just:

-1- addDtStartMonth = Convert your start month/year into a proper date

-2- addDtEndMonth = Convert your end month/year into a proper date

-3- addMonthLisy = Create a list of dates between these two

-4- expandMonthList = Expand the list of dates to new rows

 

I imagine you'll need to do a bit of jiggery-pokery with the date calculations so they work as fiscal periods rather than calendar months, but this basic structure is the way to go, I think.

 

Pete



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

Proud to be a Datanaut!




@BA_Pete this is fantastic. Thanks!

 

I'm stuck on taking this further as our real world is a bit more complex. We use fiscal months not calendar months (544 week model). I have a standard date table I can use. 

 

In our world Fiscal Year runs from July to June with Month 1 = July and Month 11 = June. But the 544 week model means the fiscal months do not fall into the calendar months. 

 

What I am unable to figure out now is in this step:

 

addMonthList =
    Table.AddColumn(
        addDtEndMonth,
        "monthList",
        each List.Distinct(
            List.Transform(
                { Number.From([dtStartMonth])..Number.From([dtEndMonth]) },
                each Date.StartOfMonth(Date.From(_))
            )
        )
    ),

 I need to generate a list of fiscal months not calendar months. I thought I could just generate the YearMonths (e.g. 202303, 202304 ... 202402) and then use my date table to join the actual months but nothing I've tried works. I must be missing some magic in how the numbers are converted back to dates.  

 

Any further thoughts/ideas?

 

Hi @vgeldbr ,

 

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", "Start Fiscal Year", "End Fiscal Month", "End Fiscal Year", "Amortization Period in months", "Per Period Amortization Amount", "period"})
in
    remOthCols

 

To get this output:

BA_Pete_1-1680180274536.png

 

Pete



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

Proud to be a Datanaut!




@BA_Pete so close!! It works perfectly where amortization spreads only from current year to the next year but if the amortization is over a longer period (e.g. 2021 to 2024) then it breaks. I guess I need to generate a separate list of fiscal years between the Start Fiscal Year and the End Fiscal Year with the Fiscal Months separate and then merge them to form the period (ie. 202107 to 202409).

vgeldbr_0-1680182870033.png

 

 

No. Should be doable just using a slightly different year calculation.

--UPDATE-- @vgeldbr  Try this:

 

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

 

 

To get this:

BA_Pete_0-1680185679404.png

 

Pete



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

Proud to be a Datanaut!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors