Sql.Database statement with parameter for filter on date

Power Query
1

I have a datasource from a SQL Server database, through the SQL statement.

= Sql.Database("sql-exact", "001", [Query="select [_].[ordernr] as [OrderNummer],#(lf)    [_].[debnr] as [DebiteurCode],#(lf)    convert(date, [_].[orddat]) as [OrderDatum],#(lf)    [_].[bdr_ev_val] as [OrderBedrag]#(lf)from #(lf)(#(lf)    select [_].[ordernr],#(lf)        [_].[debnr],#(lf)        [_].[orddat],#(lf)        [_].[bdr_ev_val]#(lf)    from [dbo].[orkrg] as [_] WITH (NOLOCK)#(lf)    where (([_].[ord_soort] = 'V' and ([_].[ord_soort] is not null)) and ([_].[fiattering] = 'J' and [_].[fiattering] is not null)) and [_].[orddat] < convert(datetime2, '2022-10-10 12:12:56.3000236')#(lf)) as [_]", CommandTimeout=#duration(0, 0, 1, 0), MultiSubnetFailover=true])

I want to filter on the date by using a dynamic parameter, where [orddat] < TODAY.

I cannot use any M query in the code, since SQL doesn’t recognize DateTime.LocalNow().

I don’t want to first get all the data and filter on date after, since getting the date for today locks or slows the database.

Any ideas or help is very much appreciated.

Thank you.

1 Like
2

Hi @JoycevW ,
Thank you very much for posting your query in the forum.
I don’t know if you have the possibility to create a view in SQL Server from the query you are using.
Example:

CREATE VIEW [dbo].[vFactInternetSales_Filtered]
ACE
SELECT SalesOrderNumber, SalesOrderLineNumber, OrderDate, ProductKey, CustomerKey, OrderQuantity, UnitPrice, SalesAmount
FROM dbo.FactInternetSales
WHERE (OrderDate < { fn NOW() })

I found this Guy in A Cube video in case it might help you.

Regards,

2 Likes
3

Thank you, I cannot create a view myself, but I can ask my collegue to do that. So it is not possible in Power BI?

4

Hi @JoycevW,
You can also put the query in the Advanced Editor instead of creating a view:

[Query="SELECT SalesOrderNumber, SalesOrderLineNumber, OrderDate, ProductKey, CustomerKey, OrderQuantity, UnitPrice, SalesAmount
FROM dbo.FactInternetSales
WHERE (OrderDate < { fn NOW() })"

Regards

5

@JoycevW I am away from system but I am sure a combination of Date.ToText ( Date.From ( DateTime.LocalNow() ) ) will query fold

6

Hi @JoycevW, did the response provided by @jafernandezpuga and @AntrikshSharma help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

7

Hi, this does not work. When I go to advanced editor it starts with:

let source = Sql.Database(etc etc,

8

So if I understand correctly you then would advise me to use the query without a date filter to get all the date and then use Power Query M to filter on date?

9

Hi @JoycevW,
This is my query in the advanced editor. In the previous answer I only copied the part of the sql query.

let
    Source = Sql.Database("JAFP\SQLEXPRESS", "AdventureWorksDW2016", [Query="SELECT SalesOrderNumber, SalesOrderLineNumber, OrderDate, ProductKey, CustomerKey, OrderQuantity, UnitPrice, SalesAmount
FROM dbo.FactInternetSales
WHERE (OrderDate < { fn NOW() })", CreateNavigationProperties=false])
in
    Source

Regards,

10

Hi @jafernandezpuga, when using your code I cannot view native query, so it doesn’t query fold. And een though the query works it does not filter the date, I still have orderdates from today. Possibly because the column orddat shows in date and time?

I have this code now:

let
Source = Sql.Database("sql-exact", "001", [Query="SELECT ordernr, debnr, orddat, bdr_ev_val, fiattering, ord_soort
FROM _Complies_vw_orkrg
WHERE (orddat < { fn NOW() })", CreateNavigationProperties=false]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([fiattering] = "J") and ([ord_soort] = "V")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"fiattering", "ord_soort"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ordernr", "OrderNummer"}, {"debnr", "DebiteurNummer"}, {"orddat", "OrderDatum"}, {"bdr_ev_val", "OrderBedrag"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"OrderDatum", type date}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([OrderBedrag] <> 0)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"OrderDatum", Order.Descending}})
in
    #"Sorted Rows"
1 Like
11

Hi @JoycevW,
As I have been able to prove, at the moment in which we use the SQL Statement section, the View Native Query option is deactivated.
We have 2 options:

  1. Create a view on the database server that contains the query we need, and then load this view into PQ.
CREATE VIEW [dbo].[vFactInternetSales_Filtered]
AS
SELECT        SalesOrderNumber, SalesOrderLineNumber, OrderDate, ProductKey, CustomerKey, OrderQuantity, UnitPrice, SalesAmount
FROM            dbo.FactInternetSales
WHERE        (CAST(OrderDate AS date) < CAST({ fn NOW() } AS date))
  1. Load the table into PQ and perform the transformations in PQ:
let
    Source = Sql.Database("JAFP\SQLEXPRESS", "AdventureWorksDW2016", [CreateNavigationProperties=false]),
    dbo_FactInternetSales = Source{[Schema="dbo",Item="FactInternetSales"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_FactInternetSales, each Date.From( [OrderDate] ) < Date.From ( DateTime.LocalNow() ) ),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"SalesOrderNumber", "SalesOrderLineNumber", "OrderQuantity", "UnitPrice", "ExtendedAmount", "OrderDateKey", "CustomerKey"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each ([SalesOrderNumber] = "SO43697" or [SalesOrderNumber] = "SO43698" or [SalesOrderNumber] = "SO43699"))
in
    #"Filtered Rows1"

image
image1364×707 53.4 KB

To filter sales with a date less than today, we convert to Date to remove the time:

#“Filtered Rows” = Table.SelectRows(dbo_FactInternetSales, each Date.From( [OrderDate] ) < Date.From ( DateTime.LocalNow() ) ),

Regards,

1 Like
12

Yes, so the only way to do this is to create a view on the server, where you can already filter the date. I was hoping I could do this only in PowerQuery. But that doesn’t work unfortunelately. Thanks for all the help!

13

Hi @JoycevW ,
You can use the second option where the query is folded.

Regards

14

Hi @JoycevW ,

I’d like to check again with you if the answer @jafernandezpuga provided above helped you solve your inquiry.

If it does, kindly mark the answer as the solution that solved your query.

If not, how far did you get, and what kind of help do you need further?

15

Hello @JoycevW , did the response above help solve your query? It’s been a while since we got a response from you. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

16

Yes, this works.

= Table.SelectRows(#“Removed Other Columns”, each Date.From([orddat])<Date.From(DateTime.LocalNow()))

If I check the Native Query it says:

where [_].[orddat] < convert(datetime2, ‘2022-11-16 00:00:00’)

But I guess the native query changes everyday? And it will not load all orders first before filtering on everything before today?

17

Hi @JoycevW,
In both options, the query changes every day.
In the second option, all the orders are loaded in power query as you can see in the Navigation step and in the next step is where we filter them.
If you don’t want all the records to be loaded into Power Query, your option is the first, create the view in the database and the filtered data arrives in Power Query.

Regards

18

So when looking at my original question:

I don’t want to first get all the data and filter on date after, since getting the date for today locks or slows the database.

I can only solve that part with a new view. I will ask our programmer to create a view for the table. Thank you!

1 Like