Forum Discussion

AlBud's avatar
AlBud
Frequent Visitor
3 years ago
Solved

M Query - If Then Else

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]...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    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.

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
3 years ago

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's avatar
AlBud
Frequent Visitor
3 years ago

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's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    You can enclose your formula in a try otherwise block.

    = try your_formula otherwise null

    • AlBud's avatar
      AlBud
      Frequent Visitor
      3 years ago

      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's avatar
        Vijay_A_Verma
        Most Valuable Professional
        3 years ago

        You need to bring otherwise null before last bracket.