Return a value based on conditions

Anonymous 

Can you check this solution if works for all your scenarios?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcvRCQAgCATQXfzuo7tymnD/NbK0CBJFfHhjCLykCNiqrzViZTODkYzjbf1Ep/Q4UZ84cd+4iUr9kinMpE0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Component = _t, #"Finish Good" = _t, hrcyc13 = _t, #"FG Identifier" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Finish Good", Int64.Type}, {"hrcyc13", Int64.Type}, {"FG Identifier", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "True Cycles", each if List.Contains({0,1,2},[FG Identifier]) then List.First(Table.SelectRows(#"Added Index", (i)=> i[Index] > [Index] and i[hrcyc13] >0 )[hrcyc13]) else "N/A"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index"})
in
    #"Removed Columns"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Anonymous 

Check my solution and let me know, I did it to solve same problem. 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Anonymous 

It's always a good idea to delete unnecessary columns in PowerQuery before making any changes for better performance and modeling.

Try the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZVNjsMgDIXv0nUr4T9slrOaQ1S9/zWGhGAaDTFRG6lRP71nbGO/348krwQvTI/nA+sDiJZzem3v2yvn7cfneQmWfJDIC/K3RJrU/lQEE0gtgPZMQEAgM+5Rsl0IApJmspBjN05gkfEGVgZF3RiKTBSlK2LG8CiyKyoqYVc0nAhmF0xxbnILEVj9zJjShWIDSwyqWyuE1upnAc9O0omiDcU4O+aK3o4y67GdQymWyLtxxpVuTIQaGRcX9LKAzuoCyXsH4+x8kXSDbKVRLw3NStPJ2pB5QYK71yrCcIeYxEFiSNptTVtqQh0AzKMtEa5OtJOSFiQe7jWhHJYd2rBSqx+v+/Q+fpF2mywLkvY7+JMK05Gw2Wz5wsRuYTkHWB9+QMpxW3JLuPE4CME04Y0s9Wu3yZWm/HdHnqZR/rsvyZVmn791xy2y1OaqGI0piFyuNBuJt0lakD6s64Us4ZU8keGV3Od6xSyNVpdpjRq5LVxdkH0FwDY2wzhPZBjnNxmPoxO51KwDtgCPRTBdQQdJmIUXZHH31dg8kWGc+8ICzRmHO047ZJASk9gXFiSOa3Qmz3F+/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [shftdte = _t, prssnbr = _t, prodcde = _t, fgprodcde = _t, hrcyc13 = _t, #"FG Identifier" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"shftdte", type date}, {"FG Identifier", Int64.Type}, {"hrcyc13", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "hrcyc13", "hrcyc13 - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",0,null,Replacer.ReplaceValue,{"hrcyc13 - Copy"}),
    #"Filled Up" = Table.FillUp(#"Replaced Value",{"hrcyc13 - Copy"}),
    #"Added Custom" = Table.AddColumn(#"Filled Up", "True Cycles", each if List.Contains({0,1,2}, [FG Identifier]) then [#"hrcyc13 - Copy"] else "N/A"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"hrcyc13 - Copy"})
in
    #"Removed Columns"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn