cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Necesidad de crear amortización como tabla en código m con valores de fila repetidos

hola

Quiero calcular los valores de amortización para cada una de mis filas de tabla de órdenes.

Mi mesa de pedidos se ve así.

Centro de gananciasfechaCantidad(in M)geoAmortización del períodoFecha de inicio de amortizaciónFecha de finalización de la amortización
P001Año Fiscal 21T1M119India131/1/20202/1/2021
P001Fy21Q1M214India132/1/20203/1/2021
P001Año Fiscal 21T1M314India133/1/20204/1/2021
P002Año Fiscal 21T1M119India134/1/20205/1/2021
P002Fy21Q1M213India135/1/20206/1/2021
P002Año Fiscal 21T1M316India136/1/20207/1/2021
P003Año Fiscal 21T1M114nos137/1/20208/1/2021
P003Fy21Q1M218nos138/1/20209/1/2021
P003Año Fiscal 21T1M311nos139/1/202010/1/2021
P004Año Fiscal 21T1M118nos1310/1/202011/1/2021
P004Fy21Q1M217nos1311/1/202012/1/2021
P004Año Fiscal 21T1M318nos1312/1/20201/1/2022

Dado que mi período de amortización es de 13 meses, significa que la diferencia entre la fecha de inicio de amortización y la fecha de finalización de amortización es de 13 meses

Based on this information I want to create an M table that will create 14(Amortization Period + 1) rows for all the months between Fecha de inicio de amortización and Fecha de finalización de la amortización with Año Mesón(Output column) as an additional column that traverses each month between the start and end month

Así que mi salida debería verse así.

A continuación se muestra la salida de la fila 1. Del mismo modo, se crearán 14 filas para cada fila de la tabla anterior

Centro de gananciasfechaCantidad(in M)geoAmortización del períodoFecha de inicio de amortizaciónFecha de finalización de la amortizaciónAño Mesón
P001Año Fiscal 21T1M119India131/1/20202/1/20211/1/2020
P001Año Fiscal 21T1M119India131/1/20202/1/20212/1/2020
P001Año Fiscal 21T1M119India131/1/20202/1/20213/1/2020
P001Año Fiscal 21T1M119India131/1/20202/1/20214/1/2020
P001Año Fiscal 21T1M119India131/1/20202/1/20215/1/2020
P001Año Fiscal 21T1M119India131/1/20202/1/20216/1/2020
P001Año Fiscal 21T1M119India131/1/20202/1/20217/1/2020
P001Año Fiscal 21T1M119India131/1/20202/1/20218/1/2020
P001Año Fiscal 21T1M119India131/1/20202/1/20219/1/2020
P001Año Fiscal 21T1M119India131/1/20202/1/202110/1/2020
P001Año Fiscal 21T1M119India131/1/20202/1/202111/1/2020
P001Año Fiscal 21T1M119India131/1/20202/1/202112/1/2020
P001Año Fiscal 21T1M119India131/1/20202/1/20211/1/2021
P001Año Fiscal 21T1M119India131/1/20202/1/20212/1/2021

¿Cómo puedo hacer esto en código m?

1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

@klehar

Pegue el código que se indica a continuación en una consulta en blanco y compruebe los pasos:

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

1 REPLY 1
Syndicate_Admin
Administrator
Administrator

@klehar

Pegue el código que se indica a continuación en una consulta en blanco y compruebe los pasos:

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

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!