Forum Discussion
Power Query Date checks
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
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.
Dear Anonymous, thank you very much for supporting me - highly appreciated!
Generally it works, but for some rows I get the following error in my dataflow:
Expression.Error: We cannot convert the value null to type Logical.
Hi, Anonymous
Thank you very much for your reply, I used the following data, you can copy and paste into Excel, then save your Excel, and set it up as below in Power BI Desktop:
Actual Start Planned Start Check Start null null 0 null 2/2/2023 0 null 2/2/2023 1 2/2/2024 2/2/2024 0 2/2/2024 1/2/2024 1 2/2/2024 null 0 thanks! In the dataflow, I get for some rows the follwing error:
Expression.Error: We cannot convert the value null to type Logical.DetailsReason = Expression.ErrorHi, Anonymous
Thank you very much for your reply, this issue may be due to a missing condition. I rewrote an M code that returns 0 when the start of the plan is equal to empty and the actual start is not equal to empty:
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 0 )