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,

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Attribute 1[/TD]
[TD]Attribute 2[/TD]
[TD]Attribute 3[/TD]
[TD]Attribute 4[/TD]
[/TR]
[TR]
[TD]Customer1[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]InvalidA[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]Customer1[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]InvalidB[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]Customer3[/TD]
[TD]InvalidC[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]InvalidD[/TD]
[/TR]
[TR]
[TD]Customer4[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Attribute[/TD]
[TD]Invalid Value[/TD]
[/TR]
[TR]
[TD]Attribute 1[/TD]
[TD]InvalidC[/TD]
[/TR]
[TR]
[TD]Attribute 3[/TD]
[TD]InvalidA[/TD]
[/TR]
[TR]
[TD]Attribute 3[/TD]
[TD]InvalidB[/TD]
[/TR]
[TR]
[TD]Attribute 4[/TD]
[TD]InvalidD[/TD]
[/TR]
</tbody>[/TABLE]

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,

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Attribute[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]Attribute 3[/TD]
[TD]InvalidA[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]Attribute 3[/TD]
[TD]InvalidB[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]Attribute 1[/TD]
[TD]InvalidC[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]Attribute 4[/TD]
[TD]InvalidD[/TD]
[/TR]
</tbody>[/TABLE]


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,