Forum Discussion
create a date column based on column condition
Hi amitchandak ,
I want to create a date field table as a result based on multiple condition.
This is data contains scheduled_tasks runs.
Data i have:
| Task | Start date | repeat by | repeat every |
| Task 1 | 01-12-23 | days | 1 |
| Task 2 | 01-12-23 | days | 2 |
based on the repeat by and repeat every , i have create a date ouput,
Condition :
Task 1 start date is 01-12-23 it run daily, so the output should be till date for task 1.
Task 2 start date is 01-12-23 it runs two day once, so the output should be alternate dates for task 2.
| Expected Result | |
| Task | Date |
| Task1 | 01-12-23 |
| Task2 | 01-12-23 |
| Task1 | 02-12-23 |
| Task1 | 03-12-23 |
| Task2 | 03-12-23 |
| Task1 | 04-12-23 |
| Task1 | 05-12-23 |
| Task2 | 05-12-23 |
output result should come upto today. if nextday coming the based on a condition we have to display which task will run that day.
need to create table till date
Hi Navaneetharaju_ ,
Just change the list formula by this one:
{0.. Number.From( Date.From (DateTime.LocalNow())- [Start date])}All the rest of the code is the same.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlYwVNJRUjAw1DU00jUyBrFTEiuLQbShUqwOVI0RLjVGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task " = _t, #"Start date" = _t, #"repeat by" = _t, #"repeat every" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task ", type text}, {"Start date", type date}, {"repeat by", type text}, {"repeat every", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {0.. Number.From( Date.From (DateTime.LocalNow())- [Start date])}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each Number.Mod ([Custom]/[repeat every] ,1) = 0 ), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = true)), #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "FInal Date", each [Start date] + #duration([Custom] ,0 , 0 , 0)) in #"Added Custom2"
3 Replies
Hi Navaneetharaju_ ,
Not sure when you want to have the end date for this but try the following:
- Add a custom column with a list from 1..100
{1..100}- Expand the list
- Add the following column
Number.Mod ([Custom]/[repeat every] ,1) = 0- Filter to only show TRUE values
- Add a new column with the following code:
[Start date] + #duration([Custom] ,0 , 0 , 0)Complete code below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlYwVNJRUjAw1DU00jUyBrFTEiuLQbShUqwOVI0RLjVGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task " = _t, #"Start date" = _t, #"repeat by" = _t, #"repeat every" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task ", type text}, {"Start date", type date}, {"repeat by", type text}, {"repeat every", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {0..100}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each Number.Mod ([Custom]/[repeat every] ,1) = 0 ), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = true)), #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "FInal Date", each [Start date] + #duration([Custom] ,0 , 0 , 0)) in #"Added Custom2"Hi MFelix ,
Last date should be a today() date.(19-dec-2023).
if i'm looking the report tommorrow , that day should be a last date.
Hi Navaneetharaju_ ,
Just change the list formula by this one:
{0.. Number.From( Date.From (DateTime.LocalNow())- [Start date])}All the rest of the code is the same.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlYwVNJRUjAw1DU00jUyBrFTEiuLQbShUqwOVI0RLjVGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task " = _t, #"Start date" = _t, #"repeat by" = _t, #"repeat every" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task ", type text}, {"Start date", type date}, {"repeat by", type text}, {"repeat every", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {0.. Number.From( Date.From (DateTime.LocalNow())- [Start date])}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each Number.Mod ([Custom]/[repeat every] ,1) = 0 ), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = true)), #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "FInal Date", each [Start date] + #duration([Custom] ,0 , 0 , 0)) in #"Added Custom2"
