Forum Discussion
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 date should be used.
My goal is to be able to list all active employments given a specified date and be able to plot employment count on a time line. Based on this requirement I have concluded that I need a new table, EmploymentDate, with two columns, EmploymentId and Date. The table will have a row for each day an employer was/is employed. Populating this table based on the Employment table is where I have gotten stuck since I’m new to power query.
How can this be implemented, or is there a better way to achieve this?
Example data, assuming today is 2019-05-31.
Started with a blank query and gotten as far as below:
ToDate = if (employment[ToDate] <> null) then employment[ToDate] else DateTimeZone.UtcNow,
DateNumberList = { Number.From(employment[FromDate]) .. Number.From(ToDate) },
DateList = List.Transform(DateNumberList, (dateNumber) => each
let
Date = Date.From(dateNumber)
in
Date
)
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.
2 Replies
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.
