none
Simple question about IF statement RRS feed

  • Question

  • Code:

    let Source = something

    #123 = if Condition1 = "True" then do_something else Source

    #124 = if Condition2 = "True" then do_something else #123

    #125 = if Condition3 = "True" then do_something else #125

    #126= if Condition4 = "True" then do_something else #126

    in

    #126

    How do I stop cycling of values ? Is there some statement like else do_nothing ?

    Code works but i think it really slows everything down because it is repeating it self.

    Thank you for answer

    Saturday, December 15, 2018 5:31 PM

Answers

  • Several things amiss with Mini849's code:

    1) Illegal identifiers - an identifier cannot begin with a '#' unless it's quoted, e.g. #"123".

    2) It is not the way stacked 'if' expressions are written. The code should read something like:

    Result = if Condition4 = "True" then do-something
             else if Condition3 = "True" then do-something
             else if Condition2 = "True" then do-something
             else if Condition1 = "True" then do-something
             else Source
    

    3) The Conditions should evaluate to a logical value instead of a text value, unless the condition being tested is truly text (if Condition4 = "Orange", if Condition3 = "Apple", etc.). So

    Result = if Condition4 = true then do-something
             else if Condition3 = true then do-something
             else if Condition2 = true then do-something
             else if Condition1 = true then do-something
             else Source

    Which then allows the simplification mentioned by Anthony:

    Result = if Condition4 then do-something
             else if Condition3 then do-something
             else if Condition2 then do-something
             else if Condition1 then do-something
             else Source

    • Marked as answer by Mini849 Thursday, January 10, 2019 7:29 AM
    Monday, December 17, 2018 12:02 AM
  • you are right.

    The example I had in mind was slighty different, something like :

    step123 = if Condition1 then #"modify Source" else #"Source"
    step124 = if Condition2 then #"modify step123" else #"step123"
    step125 = if Condition3 then #"modify step124" else #"step124"
    step126 = if Condition4 then #"modify step125" else #"step125"

    in my case, the do_something were calling back the previous steps too, which is different.

    Thank you for your explanation

    • Marked as answer by Mini849 Thursday, January 10, 2019 7:29 AM
    Monday, December 17, 2018 5:41 PM
  • you are right.

    The example I had in mind was slighty different, something like :

    step123 = if Condition1 then #"modify Source" else #"Source"
    step124 = if Condition2 then #"modify step123" else #"step123"
    step125 = if Condition3 then #"modify step124" else #"step124"
    step126 = if Condition4 then #"modify step125" else #"step125"

    in my case, the do_something were calling back the previous steps too, which is different.

    Thank you for your explanation

    No problem. Out of curiosity, what would the expression for say, #"modify step125" look like?
    • Marked as answer by Mini849 Thursday, January 10, 2019 7:29 AM
    Monday, December 17, 2018 6:04 PM
  • I needed to ensure consistency of daily csv files prepared by very unreliable sources. They kept doing typo in headers, changing or forgetting column names etc...

    My function to import the files looked like (I simplified it)

    Fn_ImportContent = (myBinary as binary) as table => let
        Source = Csv.Document(myBinary, [Delimiter=",", Columns=28, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
        PromoteHeaders = Table.PromoteHeaders(#"Source"),
    
        Header_lst = Table.ColumnNames(#"PromoteHeaders"),
        RenCol =    if List.Contains(#"Header_lst", " Net Total") 
                    then Table.RenameColumns(#"PromoteHeaders", {{" Net Total", "Net Total"}}) 
                    else #"PromoteHeaders",
        AddCol =    if not List.Contains(#"Header_lst", "Source") 
                    then Table.AddColumn(#"RenCol", "Source", each "xxx", type text) 
                    else #"RenCol",
        UpdateName = if #"Header_lst"{0}="Old Name"
                    then Table.RenameColumns(#"AddCol", {{"Old Name", "New Name"}}) 
                    else #"AddCol",
    // (...)
    Do you see nested if then else as an alternative here ?


    • Edited by anthony34 Tuesday, December 18, 2018 9:27 AM
    • Marked as answer by Mini849 Thursday, January 10, 2019 7:29 AM
    Tuesday, December 18, 2018 8:43 AM
  • Hi Anthony,

    Your code is quite different compared to the patterns we've looked at because of the referencing to previous steps in the true conditions. The approach is perfectly correct.

    The technique I would use is only slightly different, and can be turned into a custom function like you're done in your code. Note: The code below is based on combining multiple .csv files. It can be modified if you're working on one file at a time.

    let
        Source = Folder.Files(FolderPath),
        AddedCustom = 
            Table.AddColumn(
                Source, 
                "Custom", 
                each Csv.Document(
                        [Content],
                        [Delimiter=",", 
                         Encoding = 65001, 
                         CsvStyle = CsvStyle.QuoteAlways, 
                         QuoteStyle = QuoteStyle.Csv
                        ]
                     )
           ),
        PromotedHeaders = Table.TransformColumns(AddedCustom, {"Custom", each Table.PromoteHeaders(_)}),
        CombinedTables = Table.Combine(PromotedHeaders[Custom]),
        TrimmedHeaders = Table.TransformColumnNames(CombinedTables, Text.Trim),
        AddedColumn = if Table.HasColumns(TrimmedHeaders, "Source") then TrimmedHeaders
                      else Table.AddColumn(TrimmedHeaders, "Source", each "xxx"),
        UpdatedName = if Table.ColumnNames(AddedColumn){0} = "Old Name" then
                          Table.RenameColumns(AddedColumn, {"Old Name", "New Name"})
                      else AddedColumn
    in
        UpdatedName

    • Marked as answer by Mini849 Thursday, January 10, 2019 7:28 AM
    Tuesday, December 18, 2018 5:48 PM

All replies

  • fisrt, i guess your #125 and #126 have a typo: 

    • #125 = if Condition3 = "True" then do_something else #124
    • #126 = if Condition4 = "True" then do_something else #125

    Second, you can simplify if Condition3="True" then ...  by   if Condition3 then ...

    Third, I do not see why your code should slow down anything. AFAIK there is no such do_nothing statement and the else is mandatory Your code looks good to me.


    Saturday, December 15, 2018 7:12 PM
  • In addition to what Anthony has said:

    the M-language is partially lazy, meaning that it will only evaluate what's necessary.

    Usually it starts backwards, so if your Condition4 is true in the last step, the other steps wouldn't be evaluated.

    Which also means that if more than one condition is true, only the last action will be executed.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, December 16, 2018 7:28 AM
    Moderator
  • Several things amiss with Mini849's code:

    1) Illegal identifiers - an identifier cannot begin with a '#' unless it's quoted, e.g. #"123".

    2) It is not the way stacked 'if' expressions are written. The code should read something like:

    Result = if Condition4 = "True" then do-something
             else if Condition3 = "True" then do-something
             else if Condition2 = "True" then do-something
             else if Condition1 = "True" then do-something
             else Source
    

    3) The Conditions should evaluate to a logical value instead of a text value, unless the condition being tested is truly text (if Condition4 = "Orange", if Condition3 = "Apple", etc.). So

    Result = if Condition4 = true then do-something
             else if Condition3 = true then do-something
             else if Condition2 = true then do-something
             else if Condition1 = true then do-something
             else Source

    Which then allows the simplification mentioned by Anthony:

    Result = if Condition4 then do-something
             else if Condition3 then do-something
             else if Condition2 then do-something
             else if Condition1 then do-something
             else Source

    • Marked as answer by Mini849 Thursday, January 10, 2019 7:29 AM
    Monday, December 17, 2018 12:02 AM
  • If Statements
    1
    An if_statement selects for execution at most one of the enclosed sequences_of_statements, depending on the (truth) value of one or more corresponding conditions.
    Syntax
    2
    if_statement ::= 
        if condition then
          sequence_of_statements
       {elsif condition then
          sequence_of_statements}
       [else
          sequence_of_statements]
        end if;
    3
    condition ::= boolean_expression
    Name Resolution Rules
    4
    A condition is expected to be of any boolean type. 
    Dynamic Semantics
    5
    For the execution of an if_statement, the condition specified after if, and any conditions specified after elsif, are evaluated in succession (treating a final else as elsif True then), until one evaluates to True or all conditions are evaluated and yield False. If a condition evaluates to True, then the corresponding sequence_of_statements is executed; otherwise none of them is executed. 
    Examples
    6
    Examples of if statements:
    7
    if Month = December and Day = 31 then
       Month := January;
       Day   := 1;
       Year  := Year + 1;
    end if;
    8
    if Line_Too_Short then
       raise Layout_Error;
    elsif Line_Full then
       New_Line;
       Put(Item);
    else
       Put(Item);
    end if;
    9
    if My_Car.Owner.Vehicle /= My_Car then            --  see 3.10.1
       Report ("Incorrect data");
    end if;
    Monday, December 17, 2018 1:03 AM
  • If Statements
    1
    An if_statement selects for execution at most one of the enclosed sequences_of_statements, depending on the (truth) value of one or more corresponding conditions.
    Syntax
    2
    if_statement ::= 
        if condition then
          sequence_of_statements
       {elsif condition then
          sequence_of_statements}
       [else
          sequence_of_statements]
        end if;
    3
    condition ::= boolean_expression
    Name Resolution Rules
    4
    A condition is expected to be of any boolean type. 
    Dynamic Semantics
    5
    For the execution of an if_statement, the condition specified after if, and any conditions specified after elsif, are evaluated in succession (treating a final else as elsif True then), until one evaluates to True or all conditions are evaluated and yield False. If a condition evaluates to True, then the corresponding sequence_of_statements is executed; otherwise none of them is executed. 
    Examples
    6
    Examples of if statements:
    7
    if Month = December and Day = 31 then
       Month := January;
       Day   := 1;
       Year  := Year + 1;
    end if;
    8
    if Line_Too_Short then
       raise Layout_Error;
    elsif Line_Full then
       New_Line;
       Put(Item);
    else
       Put(Item);
    end if;
    9
    if My_Car.Owner.Vehicle /= My_Car then            --  see 3.10.1
       Report ("Incorrect data");
    end if;

    Hi superchampions09,

    You are just confusing the issue. Power Query does not use statements, and the syntax you provide is incorrect for Power Query.

    Monday, December 17, 2018 1:12 AM
  • Hi Colin,

    I agree with all you said, just it looks like your nested else if would change the logic and produce a different result from the initial mini849 code if, as I am assuming, the "do_something" are different in each step :

    step123 = if Condition1 then do_something1 else #"Source"
    step124 = if Condition2 then do_something2 else #"step123"
    step125 = if Condition3 then do_something3 else #"step124"
    step126 = if Condition4 then do_something4 else #"step125"

    the steps are not exluding each others: for example do_something2 and do_something4  could both be executed.

    I may be wrong, but with

    Result = if Condition4 then do-something
             else if Condition3 then do-something
             else if Condition2 then do-something
             else if Condition1 then do-something
             else Source

    only one  do_something#  would execute.

    again, all depends on how you understand the do_something in the initial post. I am assuming they are different in each step because I already happened to have such a case, and I had to code it with same logic as mini849



    • Edited by anthony34 Monday, December 17, 2018 9:04 AM
    Monday, December 17, 2018 8:55 AM
  • Hi Anthony,

    As written, it is impossible for Mini849's code to evaluate more than one condition. As Imke indicated in her earlier post, if Condition4 is true, than no other condition is evaluated. On the other hand, if Condition 4 is false, it is not evaluated and the logic falls to Condition3. If Condition3 is true, it is evaluated, and no other condition is evaluated. If Condition3 is false, the logic falls to Condition2 and so on.

    If you have a situation where more than one condition is evaluated to true, please post your code, as it could be relevant to the current discussion.

    You could evaluate more than one condition to be true by nesting if statements like so:

    if Condition4 then if ... then if ... then else ... else ... else ...

    Monday, December 17, 2018 3:14 PM
  • I disagree:

    step123 = if Condition1 then do_something1 else #"Source"
    step124 = if Condition2 then do_something2 else #"step123"
    step125 = if Condition3 then do_something3 else #"step124"
    step126 = if Condition4 then do_something4 else #"step125"

    If all the 4 conditions are false then step126 = #"Source" (lazy evaluation)

    If all the 4 conditions are true, then the 4 do_something will be executed

    The logic is different from the nested if..then..else

    Monday, December 17, 2018 4:04 PM
  • Hi Anthony,

    No. Evaluation starts at Step126 because that's the variable name that comes after the 'in' keyword. So when Step126 is evaluated, if it is true, do-something is evaluated and Step125 (the else portion) is not called (and thus never evaluated) for true or false, and the same goes for the other steps. This is how lazy evaluation works in this scenario.

    Monday, December 17, 2018 4:35 PM
  • you are right.

    The example I had in mind was slighty different, something like :

    step123 = if Condition1 then #"modify Source" else #"Source"
    step124 = if Condition2 then #"modify step123" else #"step123"
    step125 = if Condition3 then #"modify step124" else #"step124"
    step126 = if Condition4 then #"modify step125" else #"step125"

    in my case, the do_something were calling back the previous steps too, which is different.

    Thank you for your explanation

    • Marked as answer by Mini849 Thursday, January 10, 2019 7:29 AM
    Monday, December 17, 2018 5:41 PM
  • you are right.

    The example I had in mind was slighty different, something like :

    step123 = if Condition1 then #"modify Source" else #"Source"
    step124 = if Condition2 then #"modify step123" else #"step123"
    step125 = if Condition3 then #"modify step124" else #"step124"
    step126 = if Condition4 then #"modify step125" else #"step125"

    in my case, the do_something were calling back the previous steps too, which is different.

    Thank you for your explanation

    No problem. Out of curiosity, what would the expression for say, #"modify step125" look like?
    • Marked as answer by Mini849 Thursday, January 10, 2019 7:29 AM
    Monday, December 17, 2018 6:04 PM
  • I needed to ensure consistency of daily csv files prepared by very unreliable sources. They kept doing typo in headers, changing or forgetting column names etc...

    My function to import the files looked like (I simplified it)

    Fn_ImportContent = (myBinary as binary) as table => let
        Source = Csv.Document(myBinary, [Delimiter=",", Columns=28, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
        PromoteHeaders = Table.PromoteHeaders(#"Source"),
    
        Header_lst = Table.ColumnNames(#"PromoteHeaders"),
        RenCol =    if List.Contains(#"Header_lst", " Net Total") 
                    then Table.RenameColumns(#"PromoteHeaders", {{" Net Total", "Net Total"}}) 
                    else #"PromoteHeaders",
        AddCol =    if not List.Contains(#"Header_lst", "Source") 
                    then Table.AddColumn(#"RenCol", "Source", each "xxx", type text) 
                    else #"RenCol",
        UpdateName = if #"Header_lst"{0}="Old Name"
                    then Table.RenameColumns(#"AddCol", {{"Old Name", "New Name"}}) 
                    else #"AddCol",
    // (...)
    Do you see nested if then else as an alternative here ?


    • Edited by anthony34 Tuesday, December 18, 2018 9:27 AM
    • Marked as answer by Mini849 Thursday, January 10, 2019 7:29 AM
    Tuesday, December 18, 2018 8:43 AM
  • Hi Anthony,

    Your code is quite different compared to the patterns we've looked at because of the referencing to previous steps in the true conditions. The approach is perfectly correct.

    The technique I would use is only slightly different, and can be turned into a custom function like you're done in your code. Note: The code below is based on combining multiple .csv files. It can be modified if you're working on one file at a time.

    let
        Source = Folder.Files(FolderPath),
        AddedCustom = 
            Table.AddColumn(
                Source, 
                "Custom", 
                each Csv.Document(
                        [Content],
                        [Delimiter=",", 
                         Encoding = 65001, 
                         CsvStyle = CsvStyle.QuoteAlways, 
                         QuoteStyle = QuoteStyle.Csv
                        ]
                     )
           ),
        PromotedHeaders = Table.TransformColumns(AddedCustom, {"Custom", each Table.PromoteHeaders(_)}),
        CombinedTables = Table.Combine(PromotedHeaders[Custom]),
        TrimmedHeaders = Table.TransformColumnNames(CombinedTables, Text.Trim),
        AddedColumn = if Table.HasColumns(TrimmedHeaders, "Source") then TrimmedHeaders
                      else Table.AddColumn(TrimmedHeaders, "Source", each "xxx"),
        UpdatedName = if Table.ColumnNames(AddedColumn){0} = "Old Name" then
                          Table.RenameColumns(AddedColumn, {"Old Name", "New Name"})
                      else AddedColumn
    in
        UpdatedName

    • Marked as answer by Mini849 Thursday, January 10, 2019 7:28 AM
    Tuesday, December 18, 2018 5:48 PM