Forum Discussion
joshua1990
3 years agoPost Prodigy
YYYYDDD 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
- lbendlinSuper 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"- joshua1990Post Prodigy
Thanks lbendlin : But this will break query folding, right?
- lbendlinSuper User
If you are concerned about query folding then you need to do the transforms on the Oracle side, writing your own native query.
- ppm1Solution 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