Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good Afternoon,
Haven't had to use Power Query in a while, but don't understand why I am unable to filter my column [Facility] correctly.
I want to include only those rows where the text "TSI" or "HUB" does not appear anywhere in the value.
Alternatively stated, exclude all rows where either of these two text strings exist.
Below is the last line of the Advanced Editor.
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each not Text.Contains([Facility], "TSI") or not Text.Contains([Facility], "HUB")
in
#"Filtered Rows"
Am I not using this function correctly?
All help is much appreciated!
Thanks and kind regards,
Solved! Go to Solution.
bracked missing after each. Since you are using one and condition, hence brackets are needed.
Use this
= Table.SelectRows(#"Renamed Columns", each (not Text.Contains([Facility],"HUB") and not Text.Contains([Facility],"TSI")))
Alternatively, you can use an OR condition with not outside'
= Table.SelectRows(#"Renamed Columns", each not (Text.Contains([Facility],"HUB") or Text.Contains([Facility],"TSI")))
A potentially easier way to exclude values from a row would be to use the M 'does not equal' operator ('<>'). I'd suggest something like this:
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Facility] <> "TSI" and [Facility] <> "HUB"
in
#"Filtered Rows"
bracked missing after each. Since you are using one and condition, hence brackets are needed.
Use this
= Table.SelectRows(#"Renamed Columns", each (not Text.Contains([Facility],"HUB") and not Text.Contains([Facility],"TSI")))
Alternatively, you can use an OR condition with not outside'
= Table.SelectRows(#"Renamed Columns", each not (Text.Contains([Facility],"HUB") or Text.Contains([Facility],"TSI")))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.