cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
klehar
Helper II
Helper II

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

View solution in original post

Fowmy
Super User IV
Super User IV

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

Proud to be a Super User!

Website   YouTube    LinkedIn

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors