Forum Discussion
Power Query Date checks
Hi community,
I have the following columns. Could you please define the Power Query M code to create the column "Check Start"?
| Actual Start | Planned Start | Check Start |
| null | null | 0 |
| null | 2/2/2023 (could be any date before today, or today) | 0 |
| null | 2/2/2023 (could be any date after today) | 1 |
| 2/2/2024 | 2/2/2024 | 0 |
| 2/2/2024 | 1/2/2024 | 1 |
| 2/2/2024 | null | 0 |
Hi, Anonymous
Thank you very much for your reply. Sorry for the late reply. Has your problem been solved now? Maybe you can try the following code:
test = Table.AddColumn(#"Changed Type","Check start1",each if [Actual Start] = null and [Planned Start] =null then 0 else if [Actual Start] = null and [Planned Start]<= Date.From(DateTime.LocalNow()) then 1 else if [Actual Start] > [Planned Start] and [Planned Start] <> null then 1 else if [Actual Start] <> null and [Planned Start] = null then 0 else if [Actual Start] = null and [Planned Start] > Date.From(DateTime.LocalNow()) then 0 else if [Actual Start] = null and [Planned Start] < Date.From(DateTime.LocalNow()) then 0 else 0 )Best Regards
Jianpeng Li
12 Replies
What's the logic behind that column?
Hi, Anonymous
Based on your description, I use the following Sample data:
I add a new column with the following M code:
test = Table.AddColumn(#"Changed Type","Check start1",each if [Actual Start] = null and [Planned Start] =null then 0 else if [Actual Start] = null and [Planned Start]<= Date.From(DateTime.LocalNow()) then 1 else if [Actual Start] > [Planned Start] and [Planned Start] <> null then 1 else 0 )The execution result is as follows:
I have provided the PBIX file below, it would be great if it could help you.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, Anonymous
Thank you very much for your reply, I added this condition to the original M code, and the latest M code is below:
test = Table.AddColumn(#"Changed Type","Check start1",each if [Actual Start] = null and [Planned Start] =null then 0 else if [Actual Start] = null and [Planned Start]<= Date.From(DateTime.LocalNow()) then 1 else if [Actual Start] > [Planned Start] and [Planned Start] <> null then 1 else if [Actual Start] <> null and [Planned Start] = null then 0 else if [Actual Start] = null and [Planned Start] > Date.From(DateTime.LocalNow()) then 0 else 0 )
