Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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"

 

ComponentFinish Goodhrcyc13FG IdentifierTrue cycles
111112300  
1112123101100
1113    
1114 100  
3 ACCEPTED SOLUTIONS
Fowmy
Super User
Super User

@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"

Fowmy_0-1601840839864.png

________________________

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

 





Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

11 REPLIES 11
Fowmy
Super User
Super User

@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"

Fowmy_0-1601840839864.png

________________________

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

 





Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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

 

Anonymous
Not applicable

 

 
 
 
 
   How do I insert that step in my query?
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

 

Anonymous
Not applicable

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_0-1601914760060.png

 

 

Anonymous
Not applicable

dritorosas_0-1601843225120.png

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 

 

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@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. 

shftdteprssnbrprodcdefgprodcdehrcyc13FG IdentifierTrue Cycles
5/1/202021228660-0 146   
5/1/202021228660-96 124   
5/1/202021228660-G9 146   
5/1/20203 72185100-000  
5/1/202031213884-0 48   
5/1/202031237638-0 48   
5/1/20204 72101800-000  
5/1/202041012574-0 195   
5/1/20205 72262100-000  
5/1/202051072732-0 82   
5/1/20206 72205100-000  
5/1/202061011478-0 200   
5/1/202061011479-0 200   
5/1/20207 72271100-000  
5/1/202071072731-0 107   
5/1/20208 72272100-000  
5/1/202081072730-0 54   
5/1/202081259803-96 4   
5/1/20209 72332700-000  
5/1/202091259802-0 172   
5/1/202010 72121100-000  
5/1/202010 72131100-000  
5/1/2020101011477-0 230   
5/1/2020101012576-0 230   
5/1/202011 72171110-001  
5/1/202011 72171120-002  
5/1/202011 72171810-001  
5/1/202011 72171820-002  
5/1/2020111185449-0 221   
5/1/2020111185450-0 221   
5/1/202012 71474700-000  
5/1/2020121278787-0 182   
5/1/2020121278788-0 182   
5/1/2020121278789-0 182   
5/1/20201320A09431 5   
5/1/20201320A09458 5   
5/1/20201320A09466 5   
5/1/202014 71374100-000  
5/1/2020141188489-0 311   
5/1/2020141191198-0 311   
5/1/2020141191199-0 311   
5/1/2020151188489-0 242   
5/1/2020151191198-0 242   
5/1/2020151191199-0 242   
5/1/202016 71244100-000  
5/1/2020161058331-0 249   
5/1/2020161058332-0 249   
5/1/2020161058333-0 249   
5/1/202017 72181910-001  
5/1/202017 72181920-002  
5/1/2020171218808-0 151   
5/1/2020171237637-0 151   
5/1/202018 71121110-001  
5/1/202018 71121120-002  
5/1/202018 71121810-001  
5/1/202018 71121820-002  
5/1/2020181019142-0 154   
5/1/2020181032654-0 154   
5/1/202019 71171810-001  
5/1/202019 71171820-002  
5/1/2020191176624-0 129   
5/1/2020191176625-0 129   
5/1/202020 71041110-001  
5/1/202020 71041120-002  

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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

Anonymous
Not applicable

How do I insert that in my query?

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors