cancel
Showing results for
Did you mean:
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 Center Date Amount(in M) Geo Period Amortization Amortization Start Date Amortization End Date P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 P001 FY21Q1M2 14 India 13 2/1/2020 3/1/2021 P001 FY21Q1M3 14 India 13 3/1/2020 4/1/2021 P002 FY21Q1M1 19 India 13 4/1/2020 5/1/2021 P002 FY21Q1M2 13 India 13 5/1/2020 6/1/2021 P002 FY21Q1M3 16 India 13 6/1/2020 7/1/2021 P003 FY21Q1M1 14 US 13 7/1/2020 8/1/2021 P003 FY21Q1M2 18 US 13 8/1/2020 9/1/2021 P003 FY21Q1M3 11 US 13 9/1/2020 10/1/2021 P004 FY21Q1M1 18 US 13 10/1/2020 11/1/2021 P004 FY21Q1M2 17 US 13 11/1/2020 12/1/2021 P004 FY21Q1M3 18 US 13 12/1/2020 1/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 Center Date Amount(in M) Geo Period Amortization Amortization Start Date Amortization End Date YearMonth P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 1/1/2020 P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 2/1/2020 P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 3/1/2020 P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 4/1/2020 P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 5/1/2020 P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 6/1/2020 P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 7/1/2020 P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 8/1/2020 P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 9/1/2020 P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 10/1/2020 P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 11/1/2020 P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 12/1/2020 P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 1/1/2021 P001 FY21Q1M1 19 India 13 1/1/2020 2/1/2021 2/1/2021

How can i do this in m code?

1 ACCEPTED SOLUTION

@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}}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
in
#"Removed Columns"

2 REPLIES 2

@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}}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
in
#"Removed Columns"

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}}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
in
#"Removed Columns"

Proud to be a Super User!

Announcements