Forum Discussion
Return a value based on conditions
I have dataset like below. What I need to do is create a column "True Cycles" that grabs the value of the non zero value in the hrcyc13 column if the identifier = 0,1 or. 2 The Table below is an example. As of now I have created the following code but it just looks for the index +1, but the cycle is not always going to be the row below.
if [FG Identifier] = "0" or
[FG Identifier] = "1" or
[FG Identifier] ="2" then #"Added Index" [hrcyc13] {[Index]+1} else "NA"
| Component | Finish Good | hrcyc13 | FG Identifier | True cycles |
| 1111 | 1230 | 0 | ||
| 1112 | 1231 | 0 | 1 | 100 |
| 1113 | ||||
| 1114 | 100 |
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 🙂
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 🙂
11 Replies
Hi Anonymous
I am not completely sure I understood what you need but paste the following M code in a blank query to see steps of a possible solution:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgQCJR0lQyNjAyAFwgpKsTpgcSOIuCFU3BAmbgxWBMNQQROogKEB1IxYAA==", 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,{{"Component", Int64.Type}, {"Finish Good", Int64.Type}, {"hrcyc13", Int64.Type}, {"FG Identifier", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "True cycles", each if List.Contains( {0,1,2}, [FG Identifier]) then List.First(List.Select(#"Changed Type"[hrcyc13], each _<>null and _<>0)) else null), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"True cycles", Int64.Type}}) in #"Changed Type1"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Attached my dataset, maybe that explains it better. I need True Cycles to give me the value of hrcyc13 for the FG. My problem is that not always is it the cycle time of the finish good going to be in the row below, it might be a couple rows below so how do I tell power bi to look for the first non zero value and put that value where the identifier =0 or 1 or 2
Hi AlB thanks for your response, I tried it and it is only looking to the first row and putting that value to all rows. We need it to keep looking down. Attached an example of what i mean:
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 🙂
Fowmy Thank you !! I figured out how to insert it in the advanced editor. Really really appreciate your help!!
