Multiple and/or criteria in PowerPivot or Power Query

[bobbonner]

I have used PowerPivot in a limited capacity and still learning it’s capabilities. I’ve also stumbled on the amazing ETL-like things that Power Query can do. However, I struggling to see where I can apply some of the SQL logic that seems easier to accomplish in SQL Server or even Business Objects Web Intelligence. How do I do the following?

Pull in a limited data set based on multiple “and/or” criteria. For example, I want a query that does something like…

Select fields where (field a <> x and field b <> y) or (field a <> m and field b i<> n)

It’s easy enough to select fields where there are single or multiple criteria for each field stated separately, but seemingly not so much when they are in combination.

Can PowerPivot or Power Query do this?

 

[Matt Allington]

Well you could just write sql code and use that as the source in power query. Or you could add a custom column in power query that returns true for the rows you want to keep, then filter on that. The syntax is (case sensitive)

= if [column] = something then xxx else yyy