Solved: Re: Adding columns or rows for missing data

vgeldbr · ‎03-29-2023

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

Unique ID	Fiscal Year	Posting Period	Vendor	Amount in local currency	Start Fiscal Month	Start Fiscal Year	End Fiscal Month	End Fiscal Year	Amortization Period in months	Per Period Amortization Amount	Jul'22	Aug'22	Sep'22	Oct'22	Nov'22	Dec'22	Jan'23	Feb'23	Mar'23	Apr'23	May'23	Jun'23	Current FY Amortization Months Planned	No of Months Amortized	No of Amortization Months Outstanding	Months greater than 12	Current Amount	Non Current Amount	Actual Prepaid Exp Balance
400074039_5100045667_008_KR	2023	5	400074039	3499.12	4	2023	3	2024	12	291.59	0	0	0	291.59	291.59	291.59	291.59	291.59	291.59	291.59	291.59	291.59	9	6	6	0	1749.58	0	1749.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 ID	Start Fiscal Month	Start Fiscal Year	End Fiscal Month	End Fiscal Year	Current FY Amortization Months Planned	No of Months Amortized	No of Amortization Months Outstanding	Attribute	Value
400074039_5100045667_008_KR	4	2023	3	2024	9	6	6	Jul'22	0
400074039_5100045667_008_KR	4	2023	3	2024	9	6	6	Aug'22	0
400074039_5100045667_008_KR	4	2023	3	2024	9	6	6	Sep'22	0
400074039_5100045667_008_KR	4	2023	3	2024	9	6	6	Oct'22	291.59
400074039_5100045667_008_KR	4	2023	3	2024	9	6	6	Nov'22	291.59
400074039_5100045667_008_KR	4	2023	3	2024	9	6	6	Dec'22	291.59
400074039_5100045667_008_KR	4	2023	3	2024	9	6	6	Jan'23	291.59
400074039_5100045667_008_KR	4	2023	3	2024	9	6	6	Feb'23	291.59
400074039_5100045667_008_KR	4	2023	3	2024	9	6	6	Mar'23	291.59
400074039_5100045667_008_KR	4	2023	3	2024	9	6	6	Apr'23	291.59
400074039_5100045667_008_KR	4	2023	3	2024	9	6	6	May'23	291.59
400074039_5100045667_008_KR	4	2023	3	2024	9	6	6	Jun'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 ID

Start Fiscal Month

Start Fiscal Year

End Fiscal Month

End Fiscal Year

Current FY Amortization Months Planned

No of Months Amortized

No of Amortization Months Outstanding

Month.1

Month.2

Amortization

Month.1 Full

Fiscal Month Number

Fiscal Year Month Number

400074039_5100045667_008_KR

4

2023

3

2024

9

6

Jul

2022

0

July

1

202301

400074039_5100045667_008_KR

4

2023

3

2024

9

6

Aug

2022

0

August

2

202302

400074039_5100045667_008_KR

4

2023

3

2024

9

6

Sep

2022

0

September

3

202303

400074039_5100045667_008_KR

4

2023

3

2024

9

6

Oct

2022

291.59

October

4

202304

400074039_5100045667_008_KR

4

2023

3

2024

9

6

Nov

2022

291.59

November

5

202305

400074039_5100045667_008_KR

4

2023

3

2024

9

6

Dec

2022

291.59

December

6

202306

400074039_5100045667_008_KR

4

2023

3

2024

9

6

Jan

2023

291.59

January

7

202307

400074039_5100045667_008_KR

4

2023

3

2024

9

6

Feb

2023

291.59

February

8

202308

400074039_5100045667_008_KR

4

2023

3

2024

9

6

Mar

2023

291.59

March

9

202309

400074039_5100045667_008_KR

4

2023

3

2024

9

6

Apr

2023

291.59

April

10

202310

400074039_5100045667_008_KR

4

2023

3

2024

9

6

May

2023

291.59

May

11

202311

400074039_5100045667_008_KR

4

2023

3

2024

9

6

Jun

2023

291.59

June

12

202312

null

202401

null

202402

null

202403

null

202404

null

202405

null

202406

null

202407

null

202408

null

202409

null

202410

null

202411

null

202412

null

202501

null

202502

null

202503

null

202504

null

202505

null

202506

null

202507

Does anybody have any ideas or pointers?

BA_Pete · ‎03-30-2023

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:

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

BA_Pete · ‎03-29-2023

Hi @vgeldbr ,

The below query turns this:

...into 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}}),
    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!

vgeldbr · ‎03-29-2023

@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?

BA_Pete · ‎03-30-2023

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:

Pete

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

Proud to be a Datanaut!

vgeldbr · ‎03-30-2023

@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).

BA_Pete · ‎03-30-2023

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:

Pete

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

Proud to be a Datanaut!

Adding columns or rows for missing data

Helpful resources

Power BI Monthly Update - September 2024

Microsoft Fabric & AI Learning Hackathon

Fabric Community Update - September 2024