Status based on one Category

Duplicate the table (let's call it TableY). Now filter TableY to keep only the Category = Basic rows.

From the original table, do a Merge with TableY on Name (left outer join).

You can then expand only the Status column to return Pass or Fail for each row (as long as there was a Basic category row for the Name).

If there is a Name which does not have any Basic rows, you can add a conditional column to return the status.

Custom column code:

= Table.AddColumn(Source, "Basic Status",
    each let currentName = [Name],
    BasicStatus = Table.First(Table.SelectColumns(Table.SelectRows(Source,
        each [Category] = "Basic" and [Name]=currentName),"Status"))[Status]
    in BasicStatus)

Add custom column with Basic Status

All code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCihKzE4szlDSUXJKLM5MBtJuiZk5SrE6yFK+qSmZpblARkBicTGanEdiUQqyruDEvKxSJOPgWmAScMMgWmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Category = _t, Status = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Basic Status",
    each let currentName = [Name],
    BasicStatus = Table.First(Table.SelectColumns(Table.SelectRows(Source,
        each [Category] = "Basic" and [Name]=currentName),"Status"))[Status]
    in BasicStatus)
in
    #"Added Custom"

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Category]), "Category", "Status"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Basic Status", each if _[Basic]="Pass" then "Pass" else "Fail"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Name", "Basic Status"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Hope this helps.