Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I need to drop a row from my table in power query (Excel). I presume I need to filter that row out. The trouble is that other rows also have than same value and I dont want to filter out the other ones too. For example, I want to filter out
[Total]<>26 where in the same row the [Indicator] is "bob"
I do not want to filter out [Total]<>26 for "sally" and "jane"
I do not want to filter out "bob" if total does not equal 26 (since bob has other rows in other years I want to keep
I tried this
#"myfilter" = Table.SelectRows(#"previous step", each ([Total] <> 26 and [Indicator] <>"bob"))
But of course this filtered out all the 26 and all the bob entries...How do I write this please?
Best, Roger
Solved! Go to Solution.
Hi @Anonymous
You can try the below.
= Table.SelectRows(#"Changed Type", each not ( ( [Indicator] = "bob" and [Total] = 26 ) or ( [Indicator] = "jane" and [Total] = 21 ) or ( [Indicator] = "jon" and [Total] = 15 ) ) )
How can i select reow different from list, i try this but it doesn't wor?
= Table.SelectRows(#"Colonne conditionnelle ajoutée2", each [Date de base] <> #"date du jour -30"=true)
thnks
This solved my problem.
Side lesson learned: if you're renaming columns, don't use parentheses or brackets in the new name. It will screw with subsequent transformation steps.
Hi Mariusz,
What code would you use if within [Indicator] column the rows had a nested List or Table and you wanted to 'select' only the rows had items in the List. For example [Indicator] contained "Bob" and "Sally" as rows in either the nested List or nested Table?
Thanks a lot @Mariusz ,
each not() was exactly what I needed.
I also figured I could add an Index column and then filter out that unique ID.
Cheers, Roger
Hi, can I ask a further question on this,
How can I adjust the code so that this code:
= Table.SelectRows(#"Changed Type", each not ([Indicator] = "bob" and [Total] = 26))
= Table.SelectRows(#"Changed Type", each not ([Indicator] = "jane" and [Total] = 21))
= Table.SelectRows(#"Changed Type", each not ([Indicator] = "jon" and [Total] = 15))
is wrapped in something like a
each [indicator]
each if then
else if then
else
So I dont have to keep writing '= Table.SelectRows(#' ?
Thanks, Roger
Hi @Anonymous
You can try the below.
= Table.SelectRows(#"Changed Type", each not ( ( [Indicator] = "bob" and [Total] = 26 ) or ( [Indicator] = "jane" and [Total] = 21 ) or ( [Indicator] = "jon" and [Total] = 15 ) ) )
Great, thanks! That simplifies my code a little 🙂