Forum Discussion
Solved
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...
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"
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 !
