Can you filter SQL data before importing to Excel?

Afternoon all! I'm trying to import data from our SQL server to Excel, using the Get Data > From Database > From SQL Server Database button. The SQL server is the main back end of our company's ...

BI & Data Analysis

excel

office 365

fredtrev

Copper Contributor

Mar 17, 2023

Hello Sergei, after a brief spell with Covid I've managed to find some time to work on this! Power Query works very well to get the data I need into Excel, thanks for your help on that. However I now have one final question:

I need the following filters:

INCLUDE a list of results from column A (let's call them A, B and C)
INCLUDE a list of results from column B (let's call them 1, 2 and 3)
EXCLUDE a single combination of those results (e.g. A & 1)

Is this possible? I can do the first two, with two separate filters, but I can't find how to do the third.

Thanks for your help with this.

SergeiBaklan

Diamond Contributor

Mar 18, 2023

fredtrev

In general query folding shall work for such transformation as well. In Power Query steps are

- filter column A

- filter column B

- add custom column with =[A] = "a" and [B] = 1

- filter custom column keeping FALSE

- remove custom column

Generated script will be like

let
    Source = database,
    getData = Source{[Schema="dbo",Item="Table"]}[Data],

    #"Filtered Rows" = Table.SelectRows(
        getData,
        each
            ([A] = "a" or [A] = "b" or [A] = "c") and
            ([B] = 1 or [B] = 2 or [B] = 3 )),
    #"Added Custom" = Table.AddColumn(
        #"Filtered Rows",
        "Custom",
        each [A] = "a" and [B] = 1),
    #"Filtered Rows1" = Table.SelectRows(
        #"Added Custom",
        each ([Custom] = false)),
    #"Removed Columns" = Table.RemoveColumns(
        #"Filtered Rows1",
        {"Custom"})
in
    #"Removed Columns"

Assuming you didn't break query folding on previous steps, If right click on last step in above and View Native Query, generated SQL script will be like

select [_].[someField],
    [_].[A],
    [_].[B],
    [_].[anotherField]
from 
(
    select [_].someField] as [someField],
        [_].[A] as [A],
        [_].[B] as [B],
        [_].[anotherField] as [anotherField],
        case
            when [_].[A] = 'a' and [_].[B] = 1
            then 1
            when not ([_].[A] = 'a' and [_].[B] = 1)
            then 0
            else null
        end as [Custom]
    from 
    (
        select [_].[someField],
            [_].[A],
            [_].[B],
            [_].[anotherField]
        from [dbo].[Table] as [_]
        where (([_].[A] = 'a' or [_].[A] = 'b') or [_].[A] = 'c') and (([_].[B] = 1 or [_].[B] = 2) or [_].[B] = 3)
    ) as [_]
) as [_]
where [_].[Custom] = 0

Forum Discussion

Can you filter SQL data before importing to Excel?

Resources