modify rows dinamically based on condition

[Anonymous]

Hello,

I hope to define my problem properly.

I have this table 

I need to amortize each row for the consecutives 12 months.

For each row I need to create a new row dividing Value by 12, and adding a month with respect of the value of Month of the row

So after the first iteration on row 1 for example, I will find something like

Month            Value

01/01/2021 - 16160,58
01/02/2021 - 16160,58

01/03/2021 - 16160,58

01/04/2021 - 16160,58

01/05/2021 - 16160,58

01/06/2021 - 16160,58

01/07/2021 - 16160,58

01/08/2021 - 16160,58

01/09/2021 - 16160,58
01/10/2021 - 16160,58

01/11/2021 - 16160,58

01/12/2021 - 16160,58
01/02/2021 - 463351,63
... and so on.

Any help would be much appreciated.

Rosario

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can achieve it in Power Query Editor by pasting the below codes into your Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc1LDsQgDAPQu7Cu1MQmv7NU3P8anYFKoOz85Ph5GgR6/65dTYuFiGzj+nLMvDtpql4At3GaixqYDK/D+jRQglAB6/hpa4uBovQI3+STwixZ/m9vitWCWpqjB45arjUnTFLbGC8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Months", each List.Select(
  List.Dates(
    #date(Date.Year([Month]), 1, 1), 
    Number.From(#date(Date.Year([Month]), 12, 1) - #date(Date.Year([Month]), 1, 1)) + 1, 
    #duration(1, 0, 0, 0)
  ), 
  each _ = Date.StartOfMonth(_)
)),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Months"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Dates", "NValue", each [Value]/12),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Month", "Value"})
in
    #"Removed Columns"

Best Regards

[Anonymous]

Hi @v-yiruan-msft 

this was really helpful and solved my prolem. Thank you!!

The only thing is that I would want to have the column "Months" to start from the the date in the column "Month" and then add 12 months, which probably refers to this step

#"Added Custom" = Table.AddColumn(#"Changed Type", "Months", each List.Select(
List.Dates(
#date(Date.Year([Month]), 1, 1),
Number.From(#date(Date.Year([Month]), 12, 1) - #date(Date.Year([Month]), 1, 1)) + 1,
#duration(1, 0, 0, 0)

that I transformed in this one

#"Added Custom" = Table.AddColumn(#"Changed Type", "Months", each List.Select(
List.Dates(
#date(Date.Year([Month]), Date.Month([Month]), 1),
Number.From(#date(Date.Year([Month]), 12, 1) - #date(Date.Year([Month]), 1, 1)) + 1,
#duration(1, 0, 0, 0)
),
each _ = Date.StartOfMonth(_)
)),

[Anonymous]

@amitchandak 

thanks for your quick reply!

I think I did not explain myself correctly.

What I need to add 12 rows with 1/12 fo the value of each row already in the table.

So, for example, the value of 1/1/21 (1st Jan 2021) will be divided by 12 and spread until 1/12/2021 (1st dec 2021).

I figured I needed to create a new column with 1/12 of the Value column and now i have this:

now I need to understand how to "copy" the Monthly cost for 12 months for each record.

Thank you!

[amitchandak]

@Anonymous , create a end date like

end date = eomonth([month],12)

and then you can use the logic in blog or file

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785