Forum Discussion

Frequent Visitor

3 years ago

Solved

Remove rows based on condition in different columns (without column names)

Hi there, I have a sample table index V1 V2 V3 V4 V5 V6 1 1 2 3 1 2 3 2 1 -1 3 1 2 3 3 1 2 3 1 2 3 4 -1 2 3 1 2 3 5 1 2 3 1 2 3 6 1 2 ...

PowerBItes
3 years ago
No frett. Here is an idea. In PQE you can add something like "V" to all the columns you want your logic to apply to. Change your column like this:

To fix the issue in your query, you can modify the line where you add the custom column by using the following code instead:

#"Added Custom" = Table.AddColumn(#"SelectColumns", "Custom", each List.AllTrue(List.Transform(Record.ToList(_), each _ >= 0)), type logical)
This code selects only the columns that have names starting with "V" by using the Table.SelectColumns function, and then adds a custom column that checks if all the values in each row of the selected columns are greater than or equal to 0 using the List.AllTrue and List.Transform functions. The type logical argument is added to explicitly set the data type of the custom column as logical.
After this modification, the rest of your query should work as expected to filter out rows with negative values in any of the columns.

Thanks for letting me help! If you found my response useful, please give me a thumbs up and consider visiting aimeetsanalytics.com for more insights and tips on data analytics.

Community Champion

3 years ago

NewStep=Table.SelectRows(PreviousStepName,each List.Skip(Record.ToList(_),each _>=0)={})

hainguyenson
Frequent Visitor
3 years ago
This solution is OK. But I have this message: