CODEWITHSUNDEEP
powerbipowerquery
Aaron
Aaron

Reputation: 331

Power Query - Filter column in Julian Format by Today

I have a table that contains the Date in the JDE Julian Date format: CYYDDD. For instance:

01.01.2021 = 121001

Now I would like to filter this column by today. In the past I used this SQL Statement to filter the data:

DB.JDate> ( FLOOR(( EXTRACT(YEAR FROM SYSDATE) - 1900 ) / 100) 
                            || TO_CHAR(SYSDATE, 'RRDDD') )

How would you do this within Power Query?

Upvotes: 0

Views: 350

Answers (1)

Kin Siang
Kin Siang

Reputation: 2699

I never expect to obtain a date need to perform a very long calculation, nevertheless I have successfully return the output using m query, accept if help :)

Here is the actual outcome:

enter image description here

To convert Julian date to normal date, you need to this following formula, although abit long...

Date.AddDays(#date((1900 +
 Number.FromText(Text.Range(Number.ToText([Julian Date]),0,1)) *100 +
  Number.FromText(Text.Range(Number.ToText([Julian Date]),1,2))),1,1),
  Number.FromText(Text.Range(Number.ToText([Julian Date]),3))-1)

To obtain today date for filter is very easy:

DateTime.LocalNow()

Upvotes: 0

Related Questions