Excel 2016 query editor, filter/where clause

Question

I'm struggling with probably the simplest thing. How do I filter my data using a where clause in Excel 2016, ODBC sql database source. I'm new to Excel 2016, I used to just modify the SQL Command via the odbc properties. Now i can't even see the SQL command when I create the odbc.

So from just playing around. I gathered that there is now a series of "Applied Steps", but I struggle to find where I can find how to limit the rows to a date range. (I don't want to display the date because I'm aggregating by inventory type) But there must be somewhere I can say return only for date '2020-05-01' for example. (where date = '2020-05-01')

the next steps will then be making that a dynamic parameter so I can have some sort of input when the data refresh.

Any tips?

When you click All Properties under the query name on the right side, do you see information about query definition? — zedfoxus
@zedfoxus no. I only get Name (Query1) and Description (blank) and a box that you can tick "Fast Data Load" — gemmo
Okay, what happens if you click on Data source settings in the ribbon? — zedfoxus
@zedfoxus I get the name of the server and database which I chose when I set this up. When I press change source, the SQL statement can no longer be changed. (I've experimented on putting the SQL statement on the initial set up, but then I have a problem of not being able to change the date. (because it is now the "SOURCE") The SQL statement is greyed out. — gemmo
I'm not sure how to help you beyond this. You might have to get a hold of someone on your technical team. — zedfoxus

ninMonkey ninMonkey · Accepted Answer · 2020-05-31T21:05:49

The SQL statement is greyed out

Right clicking a step -> view native SQL will gray out when that step cannot be query folded. Previous steps are still viewable.

You can find the sql query and server name if you choose advanced editor -- which displays all steps in one editor.

native sql

If you want raw sql, import the table as a new connection and paste your query into that box. note: [] in powerquery is a Record literal.

// indented for readability
let
    Source = Sql.Database("server", "dbo"),
    // using parameterized queries 
    RawSQL = "SELECT * FROM users as
        WHERE EnglishMonthName=@MonthName 
        AND
        EnglishDayNameOfWeek=@DayName", 
    TableName = Value.NativeQuery(
        Source, RawSQL
        [
            MonthName="March",
            DayName="Tuesday"
        ]
    )
in
    TableName

I don't want to display the date because I'm aggregating by inventory type

Using the UI, you would import the table keeping the date column. Filter rows. Then choose remove column as your last step. Your model will not display removed columns.

Where clause in PowerQuery aka: Get Data

Where is the function Table.SelectRows The UI will generate the code for you

Right click your date column -> filter -> date/time -> equals

If you want 2020/05/09 It generates:

= Table.SelectRows(#"Source Table", each [Date] = #date(2020, 5, 9))

You may use multiple conditions, like a date range


= Table.SelectRows(#"Table Name", each
    [Date] >= #date(2020, 5, 9) and
    [Date] <= #date(2020, 6, 1)    
)