M Query - If Then Else

[AlBud]

Hi all,

I'm having trouble with the below in Power Query when creating a new custom coloumn.

if [ATA Final Destination Port] > = [ETA Final Destination Port] then [ATA Final Destination Port] + List.Average[Port To Store] else [ETA Final Destination Port] + List.Average[Port To Store]

My data has the "ETA Final Destination Port", and sometimes will have both the "ETA Final Destination Port" and the "ATA Final Destination Port". If my data has the "ATA Final Destination Port" this will be the same date or a later date than the "ETA Final Destination Port". I then want to calculate the average of my "Port To Store" across a number of rows in the same table, then add this to the "ETA Final Destination Port" if the  "ATA Final Destination Port" is missing from the table or add the average of my "Port To Store" to the "ATA Final Destination Port". The result will give me a date.

Thanks

[v-yalanwu-msft]

Hi, @AlBud ;

I don't quite understand what you mean, can you share the results you want to output?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlBud]

@v-yalanwu-msft 

The average is a number, how do I add this average number to my [ETA] to get an [ETA] + average = [Projected ATA]?

i.e. average 39.25 + 09/09/2022 = 18/10/2022

[v-yalanwu-msft · Accepted solution]

Hi, @AlBud ;

You could try it.

if [ATA Final] = null then List.Average(#"Changed Type"[Port]) else null

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlBud]

Hi @v-yalanwu-msft ,

The result I get should always be a date. As I'm trying to calculate if the [ATA Final] does not have a date, take the average of [Port] and add this to the [ETA].

From what you've sent through, the "nulls" are where I already have a date in the [ATA Final], so this is good! and I can filter this out in the BI report.

But the average is a number, how do I add this average number to my [ETA] to get an [ETA] + average = [Projected ATA]?

[v-yalanwu-msft]

Hi, @AlBud ;

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlBud]

Hi @v-yalanwu-msft , @Vijay_A_Verma 

My data has the "ETA Final Destination Port", and sometimes will have both the "ETA Final Destination Port" and the "ATA Final Destination Port". If my data has the "ATA Final Destination Port" this will be the same date or a later date than the "ETA Final Destination Port". I then want to calculate the average of my "Port To Store" across a number of rows in the same table, then add this to the "ETA Final Destination Port" if the "ATA Final Destination Port" is missing from the table or add the average of my "Port To Store" to the "ATA Final Destination Port". The result will give me a date.

Sample file: https://www.dropbox.com/home/Public#:~:text=https%3A//www.dropbox.com/s/6biikv38i0l24s4/Sample.pbix%...

I'd expect in the new column "Est Container Delivery" the highlighted fields below, where I don't have a "ATA Final Delivery" for it to average the "Port To Store" and populate with a date with either the "ETA Final Destination Port" or "ATA Final Destination Port" if populated.

[Vijay_A_Verma]

Actually the message you are getting is because there is a gap between > and =. If you delete the space between these, then you won't get message.

But still, you would get error. As List functions require a list not a column (you used List.Average)

Below is right construct where #"Previous Step" need to be replaced by your previous step

= if [ATA Final Destination Port] >= [ETA Final Destination Port] then [ATA Final Destination Port] + List.Average(#"Previous Step"[Port To Store]) else [ETA Final Destination Port] + List.Average(#"Previous Step"[Port To Store])

 👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data

[AlBud]

Hi Vijay,

That lets me create the new coloumn but I get an error now in the column. I'm wondering if this is due to me have errors in the [Port To Store].

[Vijay_A_Verma]

You can enclose your formula in a try otherwise block.

= try your_formula otherwise null

[AlBud]

Hi Vijay,

If I understood what you've suggested (I'm new to M Query) the below is what I've done and the error I get.

[Vijay_A_Verma]

You need to bring otherwise null before last bracket.