Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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
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"
If you are concerned about query folding then you need to do the transforms on the Oracle side, writing your own native query.