Home

Excel Query Editor

%3CLINGO-SUB%20id%3D%22lingo-sub-775168%22%20slang%3D%22en-US%22%3EExcel%20Query%20Editor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775168%22%20slang%3D%22en-US%22%3E%3CP%3EAnyone%20here%20know%20of%20a%20way%20I%20can%20put%20a%20function%20into%20the%20value%20of%20the%20date%20field%20that%20makes%20it%20check%20for%20the%20upcoming%20Wednesday%20and%20the%20Wednesday%20after%20that.%20Since%20I%20built%20this%20one%20Tuesday%20that%20made%20it%207%2F24%20and%207%2F31.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-775168%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775225%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Query%20Editor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775225%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382759%22%20target%3D%22_blank%22%3E%40Ghostey%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20apply%20your%20filter%20with%20dates%20using%20calendar%2C%20after%20that%20in%20Advanced%20editor%20change%20this%20step%20on%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-java%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%2F%2F%20%23%22Filtered%20Rows%22%20%3D%20Table.SelectRows(Source%2C%20each%20%5BStartDate%5D%20%26gt%3B%20%23date(2019%2C%207%2C%2031)%20and%20%5BStartDate%5D%20%26lt%3B%20%23date(2019%2C%208%2C%207))%0A%20%20%20%20%23%22Filtered%20Rows%22%20%3D%20Table.SelectRows(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20%5BStartDate%5D%20%26gt%3B%20Date.AddDays(Date.StartOfWeek(DateTime.LocalNow()%2CDay.Wednesday)%2C7)%20and%0A%20%20%20%20%20%20%20%20%20%20%20%20%5BStartDate%5D%20%26lt%3B%20Date.AddDays(Date.StartOfWeek(DateTime.LocalNow()%2CDay.Wednesday)%2C14)%0A%20%20%20%20)%0Ain%0A%20%20%20%20%23%22Filtered%20Rows%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIt%20is%20assumed%20%5BStartDate%5D%20is%20in%20datetime%20format%20and%20we%20ignore%20time%20of%20the%20day.%20If%20not%2C%20bit%20more%20adjustments%20are%20required.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775347%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Query%20Editor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775347%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHANKS!!%20This%20is%20almost%20perfect.%20What%20adjustment%20can%20I%20make%20so%20I%20can%20include%20a%20time.%20My%20field%20is%20a%20Date%20%2F%20Time%20where%20for%20the%20Start%20Date%20I%20need%20it%20to%20be%209%3A15%3A00%20AM%20on%20the%20upcoming%20and%209%3A14%3A59%20AM%20on%20the%20second.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775479%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Query%20Editor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775479%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382759%22%20target%3D%22_blank%22%3E%40Ghostey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20add%20%23duration%2C%20parameters%20are%20days%2C%20hours%2C%20minutes%2C%20seconds%2C%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%2F%2F%20%23%22Filtered%20Rows%22%20%3D%20Table.SelectRows(PrevStep%2C%20each%20%5BStartDate%5D%20%26gt%3B%20%23date(2019%2C%207%2C%2031)%20and%20%5BStartDate%5D%20%26lt%3B%20%23date(2019%2C%208%2C%207))%0A%20%20%20%20%23%22Filtered%20Rows%22%20%3D%20Table.SelectRows(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20%5BStartDate%5D%20%26gt%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Date.AddDays(Date.StartOfWeek(DateTime.LocalNow()%2CDay.Wednesday)%2C7)%20%2B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%23duration(0%2C9%2C15%2C0)%20and%0A%20%20%20%20%20%20%20%20%20%20%20%20%5BStartDate%5D%20%26lt%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Date.AddDays(Date.StartOfWeek(DateTime.LocalNow()%2CDay.Wednesday)%2C14)%20%2B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%23duration(0%2C9%2C14%2C59)%0A%20%20%20%20)%0Ain%0A%20%20%20%20%23%22Filtered%20Rows%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EExact%20seconds%20in%20duration%20depends%20on%20do%20you%20use%20%26gt%3B%20%2F%20%26lt%3B%20or%20%26lt%3B%3D%20%2F%20%26lt%3B%3D%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ghostey
New Contributor

Anyone here know of a way I can put a function into the value of the date field that makes it check for the upcoming Wednesday and the Wednesday after that. Since I built this one Tuesday that made it 7/24 and 7/31.

 

Thanks!

3 Replies

@Ghostey ,

 

You may apply your filter with dates using calendar, after that in Advanced editor change this step on something like

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // #"Filtered Rows" = Table.SelectRows(Source, each [StartDate] > #date(2019, 7, 31) and [StartDate] < #date(2019, 8, 7))
    #"Filtered Rows" = Table.SelectRows(
        Source,
        each
            [StartDate] > Date.AddDays(Date.StartOfWeek(DateTime.LocalNow(),Day.Wednesday),7) and
            [StartDate] < Date.AddDays(Date.StartOfWeek(DateTime.LocalNow(),Day.Wednesday),14)
    )
in
    #"Filtered Rows"

It is assumed [StartDate] is in datetime format and we ignore time of the day. If not, bit more adjustments are required.

@Sergei Baklan 

 

 

THANKS!! This is almost perfect. What adjustment can I make so I can include a time. My field is a Date / Time where for the Start Date I need it to be 9:15:00 AM on the upcoming and 9:14:59 AM on the second.

@Ghostey 

You may add #duration, parameters are days, hours, minutes, seconds, as

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // #"Filtered Rows" = Table.SelectRows(PrevStep, each [StartDate] > #date(2019, 7, 31) and [StartDate] < #date(2019, 8, 7))
    #"Filtered Rows" = Table.SelectRows(
        Source,
        each
            [StartDate] >
                Date.AddDays(Date.StartOfWeek(DateTime.LocalNow(),Day.Wednesday),7) +
                #duration(0,9,15,0) and
            [StartDate] <
                Date.AddDays(Date.StartOfWeek(DateTime.LocalNow(),Day.Wednesday),14) +
                #duration(0,9,14,59)
    )
in
    #"Filtered Rows"

Exact seconds in duration depends on do you use > / < or <= / <= 

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies