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
klehar
Helper V
Helper V

Need to create amortization like table in m code with repeated row values

Hi,

 

I want to calculate amortization values for each of my rows of order table. 

My order table looks like this.

 

Profit CenterDateAmount(in M)GeoPeriod AmortizationAmortization Start DateAmortization End Date
P001FY21Q1M119India131/1/20202/1/2021
P001FY21Q1M214India132/1/20203/1/2021
P001FY21Q1M314India133/1/20204/1/2021
P002FY21Q1M119India134/1/20205/1/2021
P002FY21Q1M213India135/1/20206/1/2021
P002FY21Q1M316India136/1/20207/1/2021
P003FY21Q1M114US137/1/20208/1/2021
P003FY21Q1M218US138/1/20209/1/2021
P003FY21Q1M311US139/1/202010/1/2021
P004FY21Q1M118US1310/1/202011/1/2021
P004FY21Q1M217US1311/1/202012/1/2021
P004FY21Q1M318US1312/1/20201/1/2022

 

 

Since my amortization period is 13 months, it means the difference between Amortization start date and Amortization End date is 13 months

Based on this information I want to create an M table that will create 14(Amortization Period + 1) rows for all the months between Amortization Start Date and Amortization End Date with YearMonth(Output column) as an additional column that traverses each month between the start and end month

 

So my output should look like this.

Below is the output for row 1. Similarly 14 rows will be created for each row in the above table

 

Profit CenterDateAmount(in M)GeoPeriod AmortizationAmortization Start DateAmortization End DateYearMonth
P001FY21Q1M119India131/1/20202/1/20211/1/2020
P001FY21Q1M119India131/1/20202/1/20212/1/2020
P001FY21Q1M119India131/1/20202/1/20213/1/2020
P001FY21Q1M119India131/1/20202/1/20214/1/2020
P001FY21Q1M119India131/1/20202/1/20215/1/2020
P001FY21Q1M119India131/1/20202/1/20216/1/2020
P001FY21Q1M119India131/1/20202/1/20217/1/2020
P001FY21Q1M119India131/1/20202/1/20218/1/2020
P001FY21Q1M119India131/1/20202/1/20219/1/2020
P001FY21Q1M119India131/1/20202/1/202110/1/2020
P001FY21Q1M119India131/1/20202/1/202111/1/2020
P001FY21Q1M119India131/1/20202/1/202112/1/2020
P001FY21Q1M119India131/1/20202/1/20211/1/2021
P001FY21Q1M119India131/1/20202/1/20212/1/2021

 

 

How can i do this in m code?

1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

@klehar

Paste the following code into a blank query and check the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdKxCgMhDAbgd3E+OBM99V6g0KHQUjqUw6HQpUvff+zpVXL+NHQJEfLpL2RZzNlaMoM53JkudCotzWs5vp+vRzm4UkYa2bJdW95aMnlAy2XSo2WxTrfup3ViPVj+l9mLnXTLbbyzk9ig25o5oA1iI1gHmct/b9cGo8Ckwxo4dTAJnHVY01IHZ4FkQXrI2j/Zxislnda0sae7ZSLcJg954dXdLn1vYZPzBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Profit Center" = _t, Date = _t, #"Amount(in M)" = _t, Geo = _t, #"Period Amortization" = _t, #"Amortization Start Date" = _t, #"Amortization End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Profit Center", type text}, {"Date", type text}, {"Amount(in M)", Int64.Type}, {"Geo", type text}, {"Period Amortization", Int64.Type}, {"Amortization Start Date", type date}, {"Amortization End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {0..13}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Year Month", each Date.AddMonths([Amortization Start Date],[Custom])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"



Fowmy_0-1620487033561.png

View solution in original post

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

@klehar

Paste the following code into a blank query and check the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdKxCgMhDAbgd3E+OBM99V6g0KHQUjqUw6HQpUvff+zpVXL+NHQJEfLpL2RZzNlaMoM53JkudCotzWs5vp+vRzm4UkYa2bJdW95aMnlAy2XSo2WxTrfup3ViPVj+l9mLnXTLbbyzk9ig25o5oA1iI1gHmct/b9cGo8Ckwxo4dTAJnHVY01IHZ4FkQXrI2j/Zxislnda0sae7ZSLcJg954dXdLn1vYZPzBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Profit Center" = _t, Date = _t, #"Amount(in M)" = _t, Geo = _t, #"Period Amortization" = _t, #"Amortization Start Date" = _t, #"Amortization End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Profit Center", type text}, {"Date", type text}, {"Amount(in M)", Int64.Type}, {"Geo", type text}, {"Period Amortization", Int64.Type}, {"Amortization Start Date", type date}, {"Amortization End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {0..13}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Year Month", each Date.AddMonths([Amortization Start Date],[Custom])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"



Fowmy_0-1620487033561.png

Fowmy
Super User
Super User

@klehar 

Paste the code given below on a blank Query and check the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdKxCgMhDAbgd3E+OBM99V6g0KHQUjqUw6HQpUvff+zpVXL+NHQJEfLpL2RZzNlaMoM53JkudCotzWs5vp+vRzm4UkYa2bJdW95aMnlAy2XSo2WxTrfup3ViPVj+l9mLnXTLbbyzk9ig25o5oA1iI1gHmct/b9cGo8Ckwxo4dTAJnHVY01IHZ4FkQXrI2j/Zxislnda0sae7ZSLcJg954dXdLn1vYZPzBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Profit Center" = _t, Date = _t, #"Amount(in M)" = _t, Geo = _t, #"Period Amortization" = _t, #"Amortization Start Date" = _t, #"Amortization End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Profit Center", type text}, {"Date", type text}, {"Amount(in M)", Int64.Type}, {"Geo", type text}, {"Period Amortization", Int64.Type}, {"Amortization Start Date", type date}, {"Amortization End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {0..13}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Year Month", each Date.AddMonths([Amortization Start Date],[Custom])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"



Fowmy_0-1620487033561.png

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.