YYYYDDD to gregorian date

[joshua1990]

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?

[ppm1]

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

[lbendlin]

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]

Thanks @lbendlin : But this will break query folding, right?

[lbendlin]

If you are concerned about query folding then you need to do the transforms on the Oracle side, writing your own native query.