PQ Keep Rows where any column contains a certain value

[cr731]

I am trying to isolate rows (Customers) in my data set with "invalid" values in any one of four columns. My table looks like this,

Customer	Attribute 1	Attribute 2	Attribute 3	Attribute 4
Customer1	xxx	xxx	InvalidA	xxx
Customer1	xxx	xxx	InvalidB	xxx
Customer3	InvalidC	xxx	xxx	InvalidD
Customer4	xxx	xxx	xxx	xxx

<tbody>
</tbody>

Where xxx is valid data. So based on above, I want to keep rows for Customers 1 and 3.

In each of the Attribute columns, the "invalid" value could be something different (like above, Attribute 1's invalid value is InvalidC, while Attribute 3's invalid values are InvalidA and InvalidB). So my approach was to create a table listing the invalid values by attribute.

Attribute	Invalid Value
Attribute 1	InvalidC
Attribute 3	InvalidA
Attribute 3	InvalidB
Attribute 4	InvalidD

<tbody>
</tbody>

I then unpivoted my data set, and merged with an inner join to this table of invalid values to keep all records of invalid values. Problem is, now I'm left with a table like this,

Customer	Attribute	Value
Customer 1	Attribute 3	InvalidA
Customer 1	Attribute 3	InvalidB
Customer 3	Attribute 1	InvalidC
Customer 3	Attribute 4	InvalidD

<tbody>
</tbody>

And I want my resulting output table to be the same format as the original (where there is a column for Attributes 1, 2, 3, 4).

If I try to re-pivot the last table above, I get an error that there were too many elements in the enumeration because Customer 1 has two records for Attribute 3. Is there a way to get the last table re-pivoted to look like the first, or a better way to do this altogether?

Thanks

 

[anvg]

Hi
Try with

let    source = Excel.CurrentWorkbook(){[Name="Customers"]}[Content],
    grouped = Table.Group(source, {"Customer"},
    {
        {"needed", (sub) =>
            let
                attrOnly = Table.RemoveColumns(sub, {"Customer"}),
                AttrTableList = List.Transform(Table.ToRecords(attrOnly), each Record.ToTable(_)),
                transposed = Table.Combine(AttrTableList),
                return = Table.SelectRows(transposed, each Text.StartsWith([Value], "Invalid"))
            in
                return
        }
    }),
    expanded = Table.ExpandTableColumn(grouped, "needed", {"Name", "Value"})
in
    Table.SelectRows(expanded, each [Value] <> null)

Regards,