Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
3 years ago

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

  •  

     

    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"

     

     

     

      • lbendlin's avatar
        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's avatar
    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