Forum Discussion
Solved
Filter data between from and to column dates
Hi I have an Employment table in MSSQL. It contains, among other things, three columns, one mandatory EmploymentId, one mandatory FromDate and an optional ToDate. For blank ToDate values, current da...
Hi olof
You approach is probably the best for this kind of scenario, you can do something like below, but I can see you worked it out by yourself anyway.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYzJDcAgDAR78RsJH5CEWiz6byPeYEV+zox23UmokUnn2ZXlThDAot2cNFQwX1BPwGcNVtOuenFWA93K6r84fUKN2uPMsu8X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ID " = _t, StartDate = _t, EndDate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID ", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each { Number.From([StartDate]) .. Number.From( if [EndDate] = null then Date.From( DateTime.LocalNow() ) else [EndDate] )}), #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}) in #"Changed Type1"
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi olof
You approach is probably the best for this kind of scenario, you can do something like below, but I can see you worked it out by yourself anyway.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYzJDcAgDAR78RsJH5CEWiz6byPeYEV+zox23UmokUnn2ZXlThDAot2cNFQwX1BPwGcNVtOuenFWA93K6r84fUKN2uPMsu8X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ID " = _t, StartDate = _t, EndDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID ", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each { Number.From([StartDate]) .. Number.From( if [EndDate] = null then Date.From( DateTime.LocalNow() ) else [EndDate] )}),
#"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}})
in
#"Changed Type1"
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
