Forum Discussion
How to achieve a report based on condition
Hello everyone,
I have data somewhat like this as shown below
and now I want to extract the data into the one below
Fundamentally I want to get the first Test which is not cleared and denoted as N. I have tried but not able to make that workout, any help will be appreciated.
Hi Anonymous ,
Please try:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlEwVNJRcgZiQ6VYHaiQEZDrB8RGCCFjqJAxQsgEKmSCEDKFmmUKFgoIgJiro6SAUILDOmdM65wxrUPTaIoiBLHOiCjr/FCFjFHcjeI7Y0zrjJGsM8a0zg+fdRBdJhBdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Desc = _t, #"C/N" = _t, Quality = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Desc", type text}, {"C/N", type text}, {"Quality", Int64.Type}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Quality] = null then [#"C/N"] else null), #"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Custom"}), #"Grouped Rows" = Table.Group(#"Filled Up", {"Custom"}, {{"all", each _, type table [Desc=nullable text, #"C/N"=nullable text, Quality=nullable number, Custom=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.SelectRows([all], each [#"C/N"]="N")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.2", each Table.First([Custom.1])), #"Expanded Custom.2" = Table.ExpandRecordColumn(#"Added Custom1", "Custom.2", {"Desc", "Quality"}, {"Desc", "Quality"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.2",{{"Desc", "First Test Not Cleared"}, {"Custom", "C/N"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"all", "Custom.1"}), #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,"Pass",Replacer.ReplaceValue,{"First Test Not Cleared"}), #"Added Custom2" = Table.AddColumn(#"Replaced Value", "Desc", each "PPC"), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",{"Desc", "C/N", "First Test Not Cleared", "Quality"}) in #"Reordered Columns"Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
1 Reply
Hi Anonymous ,
Please try:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlEwVNJRcgZiQ6VYHaiQEZDrB8RGCCFjqJAxQsgEKmSCEDKFmmUKFgoIgJiro6SAUILDOmdM65wxrUPTaIoiBLHOiCjr/FCFjFHcjeI7Y0zrjJGsM8a0zg+fdRBdJhBdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Desc = _t, #"C/N" = _t, Quality = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Desc", type text}, {"C/N", type text}, {"Quality", Int64.Type}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Quality] = null then [#"C/N"] else null), #"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Custom"}), #"Grouped Rows" = Table.Group(#"Filled Up", {"Custom"}, {{"all", each _, type table [Desc=nullable text, #"C/N"=nullable text, Quality=nullable number, Custom=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.SelectRows([all], each [#"C/N"]="N")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.2", each Table.First([Custom.1])), #"Expanded Custom.2" = Table.ExpandRecordColumn(#"Added Custom1", "Custom.2", {"Desc", "Quality"}, {"Desc", "Quality"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.2",{{"Desc", "First Test Not Cleared"}, {"Custom", "C/N"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"all", "Custom.1"}), #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,"Pass",Replacer.ReplaceValue,{"First Test Not Cleared"}), #"Added Custom2" = Table.AddColumn(#"Replaced Value", "Desc", each "PPC"), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",{"Desc", "C/N", "First Test Not Cleared", "Quality"}) in #"Reordered Columns"Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum