M Query for GETDATE() SQL function

Hi sbm, for current date, use this:

 

 

= DateTime.Date(
     DateTime.LocalNow()
  )

 

 

It still uses DateTime.LocalNow, but the DateTime.Date() around it gets rid of the time and timezone info. 

Now, rename that varDate (or varToday, or whatever your naming scheme is), then in the query editor, filter by any date to get the Table.SelectRows() statement set, then replace the hardcoded date the Power Query editor that will look like #date(2020,1,1) and replace with varDate.

 

 

Table.SelectRows(#"Changed Type1", each ([Date] <= varDate))

 

 

See this file. It will eliminate the May 20 data from the final result (this was posted on May 17)

By the way, this will 100% fold for you in SQL Server. The varDate value will be put in the SQL statement as a fixed amount, which will change every day the thing runs.

 

EDIT: I just noticed you put in a native query yourself. Don't do that if possible. You should always try to have Power Query generate the SQL for you, and using a native query breaks that feature. 

 

The file has a connection to the ContosoRetailDW DimDate table (you can change the db on your side). This db has date/time in the date field, so I created a new varDateTime. It runs the following SQL statement for me based on this select statement:

Table.SelectRows(dbo_DimDate, each ([Datekey] >= varDateTime))

Of course nothing is returned as all of the data in that database is way older than 2020, but it would work with real data.

select [_].[Datekey],
    [_].[FullDateLabel],
    [_].[DateDescription],
    [_].[CalendarYear],
    [_].[CalendarYearLabel],
    [_].[CalendarHalfYear],
    [_].[CalendarHalfYearLabel],
    [_].[CalendarQuarter],
    [_].[CalendarQuarterLabel],
    [_].[CalendarMonth],
    [_].[CalendarMonthLabel],
    [_].[CalendarWeek],
    [_].[CalendarWeekLabel],
    [_].[CalendarDayOfWeek],
    [_].[CalendarDayOfWeekLabel],
    [_].[FiscalYear],
    [_].[FiscalYearLabel],
    [_].[FiscalHalfYear],
    [_].[FiscalHalfYearLabel],
    [_].[FiscalQuarter],
    [_].[FiscalQuarterLabel],
    [_].[FiscalMonth],
    [_].[FiscalMonthLabel],
    [_].[IsWorkDay],
    [_].[IsHoliday],
    [_].[HolidayName],
    [_].[EuropeSeason],
    [_].[NorthAmericaSeason],
    [_].[AsiaSeason]
from [dbo].[DimDate] as [_]
where [_].[Datekey] >= convert(datetime2, '2020-05-17 17:58:47.8988703')

 

Do some other transformations. You'll be surprised what will fold. You can group, add columns, do basic and even a few nested if/then/else statements, merges, appends, etc. Not everything folds, but I've brought over 10 tables in to a Power Query session, did numerous transformations, merges, etc. and ultimately generated an over 800 line SQL statement befor I had to do a few final transformations that broke folding. That means the SQL server was doing 95% of my work for me. Always try to avoid manual SQL statements in the advanced view.