Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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}}),
#"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"
@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"
@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"
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |