Forum Discussion
fredtrev
Mar 09, 2023Copper Contributor
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 ...
SergeiBaklan
Mar 09, 2023Diamond Contributor
In general Query folding - Power Query | Microsoft Learn shall work. Alternatively you may use SQL statement quiring the database.
Just don't load result into the grid before you finalized your query.
- fredtrevMar 17, 2023Copper ContributorHello 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.- SergeiBaklanMar 18, 2023Diamond Contributor
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 - peiyezhuMar 17, 2023Bronze ContributorSQL Server Database
create view on sql server?
if yes,share some data and your expectrd result.
- fredtrevMar 09, 2023Copper ContributorThanks Sergei, that certainly looks like it would work. I'll try that when I'm back in the office and can access the server!