Forum Discussion

fredtrev's avatar
fredtrev
Copper Contributor
Mar 09, 2023

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 ERP system, so it has a huge amount of data.  When I try to pull it through into Excel, it basically crashes Excel because there are over a million lines.  Is there any way to filter the SQL data before pulling it into Excel?  I only need to look at a tiny proportion of the lines in the database.

BI & Data Analysis
excel
office 365

5 Replies

    • fredtrev's avatar
      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's avatar
        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

         

         

    • fredtrev's avatar
      fredtrev
      Copper Contributor
      Mar 09, 2023
      Thanks Sergei, that certainly looks like it would work. I'll try that when I'm back in the office and can access the server!

Resources