Forum Discussion
Adding new calcualted row based on a condition
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
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"
3 Replies
Hi tomislav_mi
how about the value in the "Status"-column? Shall it always be "Lost" for those new rows?
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"Thank you a lot ImkeF !
