Forum Discussion
joshua1990
Post Prodigy
3 years agoYYYYDDD to gregorian date
Hi all!
I have a column in a oracle DB that contains the date in the format YYYYDDD.
To performane an incremental refresh, I need to translate this format into YYYY-MM-DD, but how?
4 Replies
- lbendlin
Super User
let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText("i45WMjIwMjIwMFSK1YGwDc1MlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t] ), #"Added Custom" = Table.AddColumn( Source, "Custom", each Date.ToText( Date.AddDays( Date.From(Text.Start([Column1], 4) & "-01-01"), Int32.From(Text.End([Column1], 3)) - 1 ), "yyyy-mm-dd" ) ) in #"Added Custom"- joshua1990
Post Prodigy
Thanks lbendlin : But this will break query folding, right?
- lbendlin
Super User
If you are concerned about query folding then you need to do the transforms on the Oracle side, writing your own native query.
- ppm1
Solution Sage
Instead of converting your date column, you can convert your RangeStart and RangeEnd parameters, and then use those values in your filter step. It will still fold. Here is an example.
let RSasYYYYDDD = 1000 * Date.Year(RangeStart) + Date.Day(RangeStart), REasYYYYDDD = 1000 * Date.Year(RangeEnd) + Date.Day(RangeEnd), Source = Sql.Database("localhost", "Flights", [CreateNavigationProperties=false]), dbo_Date = Source{[Schema="dbo",Item="Date"]}[Data], #"Filtered Rows" = Table.SelectRows(dbo_Date, each [Date] >= RSasYYYYDDD and [Date] < REasYYYYDDD) in #"Filtered Rows"Pat