How to achieve a report based on condition

[Anonymous]

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.

[v-cgao-msft · Accepted solution]

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 Team

If 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