Power Query - importing OData into excel and filtering on date.

darkgen

darkgen

darkgen

Soldato
Joined
8 Mar 2005
Posts
3,934
Location
London, UK
Any power query heroes here? I've only ever touched the Excel transform data wizard and not needed to go under the hood as it were when pulling in data. However, this time I need to pull in data just for the previous 90 days as the dataset is extremely large.

The table contains various timestamp columns which I want to filter on but how you plug that into PQ I've no idea.

When viewing the advanced connection query, it is nothing more complex than:
Code: 
let
    Source = OData.Feed("http://hostname/Data", null, [Implementation="2.0"]),
    Connections_table = Source{[Name="Connections",Signature="table"]}[Data]
in
    Connections_table
I understand PQ accepts now(); so was trying to fathom how I then plug in the relevant code to return only rows in the last 90 days, i.e. where timestamp_column between now() and now()-90 or somesuch.

*scratch head*
 
It uses the M language I believe.

Just filter the column using the downward pointing arrow in your PQ Editor select 'Date Filters'. You can even load this to a new query to mess about with.
 
Th0nt said:
It uses the M language I believe.

Just filter the column using the downward pointing arrow in your PQ Editor select 'Date Filters'. You can even load this to a new query to mess about with.
Click to expand...
Oh, what an idiot. I'm looking at the banner and utterly missing the column drop-down!

... and the advanced query updates showing the necessary query.

:)
 
If you need any burden shared, post it here. I had to teach myself it for a project we supported. Going to revisit it as I want to get BI sorted sometime this year.
 
You must log in or register to reply here.
Back
Top Bottom