March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 | 3 | -1 | 2 | 3 |
7 | 1 | 2 | 3 | 1 | 2 | 3 |
8 | 1 | 2 | 3 | 1 | 2 | -1 |
9 | 1 | 2 | 3 | 1 | 2 | 3 |
10 | 1 | 2 | 3 | 1 | -1 | 3 |
I want to remove rows that have negative value in each column: V1, V2, V3, V4, V5, V6.
My result I want is:
index | V1 | V2 | V3 | V4 | V5 | V6 |
1 | 1 | 2 | 3 | 1 | 2 | 3 |
3 | 1 | 2 | 3 | 1 | 2 | 3 |
5 | 1 | 2 | 3 | 1 | 2 | 3 |
7 | 1 | 2 | 3 | 1 | 2 | 3 |
9 | 1 | 2 | 3 | 1 | 2 | 3 |
Currently, I'm using this query, the result is OK but it is very long:
Filter1 = Table.SelectRows(#"Changed Type", each [#"V1"] >= 0)
Filter2 = Table.SelectRows(#"Filter1", each [#"V2"] >= 0)
Filter3 = Table.SelectRows(#"Filter2", each [#"V3"] >= 0)
Filter4 = Table.SelectRows(#"Filter3", each [#"V4"] >= 0)
Filter5 = Table.SelectRows(#"Filter4", each [#"V5"] >= 0)
Filter6 = Table.SelectRows(#"Filter5", each [#"V6"] >= 0)
I will have problem if I have many columns. My idea is I will iterate all column start with V, I'm following this topic https://community.powerbi.com/t5/Power-Query/Remove-rows-based-on-condition-in-different-columns/m-p... and trying another query:
= let
#"SelectColumns" = Table.SelectColumns(Source,List.Select(
Table.ColumnNames(#"Changed Type"),
each Text.Contains(_,"V"))),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Table.SelectColumns(#"SelectColumns") >= 0 then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
#"Removed Columns"
I have a problem with step: #"Added Custom".
How can I fix it? Please help.
Thanks in advanced.
File demo:
https://drive.google.com/file/d/1iTwykAmqw-g-6pdBEIAEQ9TRyuc5OYxk/view?usp=sharing
Solved! Go to Solution.
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.
NewStep=Table.SelectRows(PreviousStepName,each List.Skip(Record.ToList(_),each _>=0)={})
This solution is OK. But I have this message:
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.
That's what I'm looking for. Thank you.
Hi,
you can insert a column
and then filter -1
If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
24 | |
13 | |
11 | |
10 | |
8 |
User | Count |
---|---|
43 | |
26 | |
18 | |
16 | |
11 |