Return a value based on conditions

[dritorosas]

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

[Fowmy · Accepted solution]

@dritorosas 

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

[Fowmy · Accepted solution]

@dritorosas 

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

[Fowmy · Accepted solution]

@dritorosas 

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

[dritorosas]

@Fowmy  Thank you !! I figured out how to insert it in the advanced editor. Really really appreciate your help!!

[dritorosas]

@Fowmy 

   How do I insert that step in my query?

[AlB]

Hi @dritorosas 

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 

[dritorosas]

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:

[dritorosas]

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 

[Fowmy · Accepted solution]

@dritorosas 

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

[dritorosas]

@Fowmy

Below is a sample of the data. It's 500,000 rows and 34 columns but I only use 7 so I delete the rest in power bi. 

shftdte	prssnbr	prodcde	fgprodcde	hrcyc13	FG Identifier	True Cycles
5/1/2020	2	1228660-0		146
5/1/2020	2	1228660-96		124
5/1/2020	2	1228660-G9		146
5/1/2020	3		72185100-0	0	0
5/1/2020	3	1213884-0		48
5/1/2020	3	1237638-0		48
5/1/2020	4		72101800-0	0	0
5/1/2020	4	1012574-0		195
5/1/2020	5		72262100-0	0	0
5/1/2020	5	1072732-0		82
5/1/2020	6		72205100-0	0	0
5/1/2020	6	1011478-0		200
5/1/2020	6	1011479-0		200
5/1/2020	7		72271100-0	0	0
5/1/2020	7	1072731-0		107
5/1/2020	8		72272100-0	0	0
5/1/2020	8	1072730-0		54
5/1/2020	8	1259803-96		4
5/1/2020	9		72332700-0	0	0
5/1/2020	9	1259802-0		172
5/1/2020	10		72121100-0	0	0
5/1/2020	10		72131100-0	0	0
5/1/2020	10	1011477-0		230
5/1/2020	10	1012576-0		230
5/1/2020	11		72171110-0	0	1
5/1/2020	11		72171120-0	0	2
5/1/2020	11		72171810-0	0	1
5/1/2020	11		72171820-0	0	2
5/1/2020	11	1185449-0		221
5/1/2020	11	1185450-0		221
5/1/2020	12		71474700-0	0	0
5/1/2020	12	1278787-0		182
5/1/2020	12	1278788-0		182
5/1/2020	12	1278789-0		182
5/1/2020	13	20A09431		5
5/1/2020	13	20A09458		5
5/1/2020	13	20A09466		5
5/1/2020	14		71374100-0	0	0
5/1/2020	14	1188489-0		311
5/1/2020	14	1191198-0		311
5/1/2020	14	1191199-0		311
5/1/2020	15	1188489-0		242
5/1/2020	15	1191198-0		242
5/1/2020	15	1191199-0		242
5/1/2020	16		71244100-0	0	0
5/1/2020	16	1058331-0		249
5/1/2020	16	1058332-0		249
5/1/2020	16	1058333-0		249
5/1/2020	17		72181910-0	0	1
5/1/2020	17		72181920-0	0	2
5/1/2020	17	1218808-0		151
5/1/2020	17	1237637-0		151
5/1/2020	18		71121110-0	0	1
5/1/2020	18		71121120-0	0	2
5/1/2020	18		71121810-0	0	1
5/1/2020	18		71121820-0	0	2
5/1/2020	18	1019142-0		154
5/1/2020	18	1032654-0		154
5/1/2020	19		71171810-0	0	1
5/1/2020	19		71171820-0	0	2
5/1/2020	19	1176624-0		129
5/1/2020	19	1176625-0		129
5/1/2020	20		71041110-0	0	1
5/1/2020	20		71041120-0	0	2

[Fowmy · Accepted solution]

@dritorosas 

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

[dritorosas]

Thank you, this worked. Quick question though, what did the "Copy" thing do? I'm just trying to understand. It did work, I just want to understand the logic.

Thanks

[dritorosas]

How do I insert that in my query?