Query filtering using Today() as a date ?

[chuggins143]

Hey all,

Okay, I'm pulling in stock market data... specifically dividend data which I can filter by dates. (example data... Eagle Point Credit Company Inc. (ECC) Dividend History, Dates & Yield - Stock Analysis) I can open the Power Query Editor and use the Date Filter on the Ex-Dividend Date column but I'm limited to only using specific dates... is there a way to filter "is before or equal to" Today()??

I'm new to the Power Tools so this is a learning experience for me. I'm not finding much out there so a point in the right direction would be appreciated.

Thank you,
C

 

[alansidman]

let
    Source = Web.BrowserContents("https://stockanalysis.com/stocks/ecc/dividend/"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(1)"}, {"Column2", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(2)"}, {"Column3", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(3)"}, {"Column4", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(4)"}}, [RowSelector="TABLE.svelte-1jtwn20 > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Ex-Dividend Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [#"Ex-Dividend Date"] < Date.From(DateTime.FixedLocalNow()) then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
    #"Filtered Rows"

 

[johnny51981]

This also works.

let
    Source = Web.Page(Web.Contents("https://stockanalysis.com/stocks/ecc/dividend/")),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Source] = "Table")),
    Data0 = #"Filtered Rows"{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"Data0",{{"Ex-Dividend Date", type date}}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each [#"Ex-Dividend Date"] <= Date.From(DateTime.FixedLocalNow()))
in
    #"Filtered Rows2"

 

[chuggins143]

I obviously did something wrong... I get this error... "Expression.Error: The name 'Html.Table' wasn't recognized. Make sure it's spelled correctly." The advanced editor tells me that there are no syntax errors so...

let
    Source = Web.BrowserContents("https://stockanalysis.com/stocks/ecc/dividend/"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(1)"}, {"Column2", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(2)"}, {"Column3", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(3)"}, {"Column4", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(4)"}}, [RowSelector="TABLE.svelte-1jtwn20 > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Ex-Dividend Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [#"Ex-Dividend Date"] < Date.From(DateTime.FixedLocalNow()) then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
    #"Filtered Rows"

 

[chuggins143]

I did get this to work as it's closer to what I'm seeing on my current queries... Follow up question, how can I add a few days to the Now()? So say I want it to be 3 days in the future? I tried just adding a "+3" to it, but the syntax didn't like that.

This also works.

let
    Source = Web.Page(Web.Contents("https://stockanalysis.com/stocks/ecc/dividend/")),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Source] = "Table")),
    Data0 = #"Filtered Rows"{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"Data0",{{"Ex-Dividend Date", type date}}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each [#"Ex-Dividend Date"] <= Date.From(DateTime.FixedLocalNow()))
in
    #"Filtered Rows2"

 

[chuggins143]

Thank you both for the responses!
C

 

[johnny51981]

You'd change
Date.From(DateTime.FixedLocalNow())

to
Date.AddDays(Date.From(DateTime.FixedLocalNow()),3)

 

[chuggins143]

You'd change
Date.From(DateTime.FixedLocalNow())

to
Date.AddDays(Date.From(DateTime.FixedLocalNow()),3)

Excellent! I have not tried that... Again, I'm still a bit new to the whole query side of Excel, so I'm not familiar with the syntax yet. I'm good with most Excel syntax and writing macros and such just not this yet. Is it based in some other language? Obviously, I have not taken the time to dig into it too deep... Thanks!

 

[alansidman]

Look here-->https://www.amazon.com/Data-Monkey-...7358&refinements=p_27:Ken+Puls&s=books&sr=1-2