Adding new calcualted row based on a condition

[tomislav_mi]

Hey guys!

I am diving into DAX and Power Query and I am fascinated by how much it can be done with it and how much other people know. And sharing that knowledge as well!!

I have encountered one of the trickiest challenges so far and would be so happy if somebody can help me out.

This is my dataset and would need a calculated ROW (desired row in orange) following a certain logic:

if [Filter]=1 then insert a new row with [Ending MRR] = 0 and [Report Date] + 1 month and copy the rest of values for other columns.

Account Name	Report Date	Ending MRR	MRR change	Status	Filter
a	9/30/2019	400	400	New
a	10/31/2019	400	0	-
a	11/30/2019	553	153	Up
a	12/31/2019	158	-395	Down
a	1/31/2020	79	-79	Down	1
a	2/29/2020	0	-79	Lost

(maybe this will be helpful:

Column [Filter] is calculated column following logic = if Report Date is not maximum Report Date (usually there are values after 1/31/2020 in original dataset) then 1)

If there is any but really any other way how to add it, please let me know.

Many thanks to the one who is able to solve this challenge.

Tomislav

[ImkeF]

Hi @tomislav_mi  

how about the value in the "Status"-column? Shall it always be "Lost" for those new rows?

[ImkeF · Accepted solution]

Hi

please check this solution and let me know if you want to change the handling of the Status column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc4xDoAgDAXQuzBDaEEi7M5uToRBE1d14/rSYhQc+CXk5ZcYxSqkCNqCNoCh3AeAN+c9lxRJVoagLfaOjuoMNl3OWXriXK6OmaYKnacaG1wZ05mPTlZoaNNIWnE+DNltP+f5W5yfSzc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Account Name" = _t, #"Report Date" = _t, #"Ending MRR" = _t, #"MRR change" = _t, Status = _t, Filter = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Name", type text}, {"Report Date", type text}, {"Ending MRR", Int64.Type}, {"MRR change", Int64.Type}, {"Status", type text}, {"Filter", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Report Date", type date}}, "en-US"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 0, 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Filter] = 1)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",each [Ending MRR],0,Replacer.ReplaceValue,{"Ending MRR"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Status],"Lost",Replacer.ReplaceText,{"Status"}),
    AddMonth = Table.TransformColumns(#"Replaced Value1",{{"Report Date", each Date.AddMonths(_,1), type date}}),
    Custom1 = #"Added Index" & AddMonth,
    #"Sorted Rows" = Table.Buffer( Table.Sort(Custom1,{{"Index", Order.Ascending}, {"Report Date", Order.Ascending}}) )
in
    #"Sorted Rows"

[tomislav_mi]

Thank you a lot @ImkeF !