find response of "better" or "worse" where "NA" or "No Change" present in latest record - POWERBI

[RyanVSS]

I have a mind block for coming up with a creative solution to my problem. Essentially, i have clients that report to me on session outcomes throughout the life of their account. The feedback can be NA, better, slightly better, worse, slightly worse, no change. from this feedback i need to find out the most recent outcome of better, slightly better, worse or slightly worse. this is used to indicate if the client is currenty positive or negative. 

therefore, i need to create a PowerBI table whereby if latest submission has the feedback of "NA" or "No "Change", then loop through previous sessions (most recent first) until a non-NA and non-No Change answer was provided. A new record should be created in the table which combines the latest feedback that's non-NA and non-No Change

Desired output

Regarding Obect ID: unique client 

Actual End: Latest session date

Outcome: Latest non-NA and non-No Change

 Outcome: Latest non-NA and non-No Change

Outcome: Latest non-NA and non-No Change

 Outcome: Latest non-NA and non-No Change 

Example:

[ryan_mayu · Accepted solution]

you can do this in PQ

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JTM0rMVTSUTIyMDLRN9E3NAKy/RzRiVgdNLVG+sb6xgYwFeX5RcWpQDoptaQktQinFiN9MyAzHF0xTCA4JzM9oySnUsEJIoMwwQhigjHQBEOwpfkKzhmJeempqO6Ei2LoNNK30DcyRVGNbhuao+EaTSFhAleFqQ/mfpizYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Regarding ObjectID" = _t, LastestSession = _t, Outcome1 = _t, Outcome2 = _t, Outcome3 = _t, Outcome4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Regarding ObjectID", type text}, {"LastestSession", type date}, {"Outcome1", type text}, {"Outcome2", type text}, {"Outcome3", type text}, {"Outcome4", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Regarding ObjectID", Order.Ascending}, {"LastestSession", Order.Descending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","NA",null,Replacer.ReplaceValue,{"Outcome1", "Outcome2", "Outcome3", "Outcome4"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","No Change",null,Replacer.ReplaceValue,{"Outcome1", "Outcome2", "Outcome3", "Outcome4"}),
#"Filled Up" = Table.FillUp(#"Replaced Value1",{"Outcome1", "Outcome2", "Outcome3", "Outcome4"}),
Custom1 = Table.AddColumn(#"Filled Up","Rank",each Table.RowCount(Table.SelectRows(#"Filled Up",(x)=>x[LastestSession]>[LastestSession] and x[Regarding ObjectID]=[Regarding ObjectID]))+1),
#"Filtered Rows" = Table.SelectRows(Custom1, each ([Rank] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Rank"})
in
#"Removed Columns"

pls see the attachment below

[RyanVSS]

hi - sorry to come back again - firstly, the solution worked as intended for sample size data, but when i apply the solution to actual dataset the model fails to process as a dataflow on the PBI service. i get the error "failed to analyze" at the initial stage when checking for errors. 

when i break it down on PBI desktop i notice that the model trips up when pivoting/unpivoting the data. im not sure if there is any way around this?  I have also tried to break the script into parts and process as linked model, but it doesn't work because of the Pivot/Unpivot functions.   

[RyanVSS]

thank you for the solution and the pbix to help me understand the solution. i have a followup question for those clients where there are "NA" or "Not Recorded" for all sessions, how do i make sure that the fill up leaves those client answers as null or "NA"/"Not Recorded" and not fill up from a previous client down the list? 

client 1 fill up should be 1 - worse, 2 - null, 3 - null, 4 - null 

however, the fill up has taken answers from previous clients

[ryan_mayu · Accepted solution]

@RyanVSS 

i update the sample data due to your real situation

pls try this

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JTM0rMVTSUTIyMDLRN9E3NAKy/RzRiVgdNLVG+sb6xgYwFeX5RcWpQDoptaQktQinFiN9M5gOuMpwqNbgnMz0jJKcSgUniAxCuxFEuzFQuyHYxnwF54zEvPRUVEfCRTF0Gulb6BuZoqhGtw3NxXCNppAAgavC1AdzP8zZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Regarding ObjectID" = _t, LastestSession = _t, Outcome1 = _t, Outcome2 = _t, Outcome3 = _t, Outcome4 = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Regarding ObjectID", "LastestSession"}, "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"LastestSession", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Value] <> "NA")),
Custom1 = Table.AddColumn( #"Filtered Rows1","Rank",each Table.RowCount(Table.SelectRows( #"Filtered Rows1",(x)=>x[LastestSession]<[LastestSession] and x[Regarding ObjectID]=[Regarding ObjectID] and x[Attribute]=[Attribute]))+1),
#"Filtered Rows" = Table.SelectRows(Custom1, each ([Rank] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Rank", "LastestSession"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Regarding ObjectID", "Outcome1", "Outcome2", "Outcome3", "Outcome4"})
in
#"Reordered Columns"

pls see the attachment below

[ryan_mayu · Accepted solution]

you can do this in PQ

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JTM0rMVTSUTIyMDLRN9E3NAKy/RzRiVgdNLVG+sb6xgYwFeX5RcWpQDoptaQktQinFiN9MyAzHF0xTCA4JzM9oySnUsEJIoMwwQhigjHQBEOwpfkKzhmJeempqO6Ei2LoNNK30DcyRVGNbhuao+EaTSFhAleFqQ/mfpizYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Regarding ObjectID" = _t, LastestSession = _t, Outcome1 = _t, Outcome2 = _t, Outcome3 = _t, Outcome4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Regarding ObjectID", type text}, {"LastestSession", type date}, {"Outcome1", type text}, {"Outcome2", type text}, {"Outcome3", type text}, {"Outcome4", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Regarding ObjectID", Order.Ascending}, {"LastestSession", Order.Descending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","NA",null,Replacer.ReplaceValue,{"Outcome1", "Outcome2", "Outcome3", "Outcome4"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","No Change",null,Replacer.ReplaceValue,{"Outcome1", "Outcome2", "Outcome3", "Outcome4"}),
#"Filled Up" = Table.FillUp(#"Replaced Value1",{"Outcome1", "Outcome2", "Outcome3", "Outcome4"}),
Custom1 = Table.AddColumn(#"Filled Up","Rank",each Table.RowCount(Table.SelectRows(#"Filled Up",(x)=>x[LastestSession]>[LastestSession] and x[Regarding ObjectID]=[Regarding ObjectID]))+1),
#"Filtered Rows" = Table.SelectRows(Custom1, each ([Rank] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Rank"})
in
#"Removed Columns"

pls see the attachment below