Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

IF with multiple statements in M language

Hi, in Power Query, by a custom M code, is it possible to use IF with more statements for a satisfied condition? In another terms, is it possibile to have a block of instructions to run for an IF, d...
Need Help
v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

Hi Anonymous ,

 

Is your issue solved now?

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Which other details do you need?

I asked if it is possible to write in M language a such IF with a statement block:

IF <condition>

BEGIN

<statement 1>

<statement 2>

<statement 3>

...

<statement n>

END

In other programming language this is possible (e.g. C, C++, T-SQL, etc.).

Thanks

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    The description in your post is too general,since we have shown you plenty of references which should be helpful,if they dont help,show us the details for the [statement],you could paste some sample data with expected output to let us know your requirement more clear.

    Or you could paste a sql query such as below:

     

     DECLARE @sales INT;
SELECT @sales = SUM(OrderQty * UnitPrice)
FROM [AdventureWorks2017].[Sales].[SalesOrderDetail];
IF @sales > 100000000
    SELECT *
    FROM [AdventureWorks2017].[Sales].[SalesOrderDetail];
    ELSE
    SELECT *
    FROM [AdventureWorks2017].[Sales].[SalesOrderHeader];

     

     

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi,

      my question is simply: "is it possible to write an IF with a statement block?". I'd like to know the capabilities of the IF construct, also without indicating a specific case.

      As I said, T-SQL allows to use the IF construct with a statement block:

      USE AdventureWorks2012;  
GO  
DECLARE @AvgWeight DECIMAL(8,2), @BikeCount INT  
IF   
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5  
BEGIN  
   SET @BikeCount =   
        (SELECT COUNT(*)   
         FROM Production.Product   
         WHERE Name LIKE 'Touring-3000%');  
   SET @AvgWeight =   
        (SELECT AVG(Weight)   
         FROM Production.Product   
         WHERE Name LIKE 'Touring-3000%');  
   PRINT 'There are ' + CAST(@BikeCount AS VARCHAR(3)) + ' Touring-3000 bikes.'  
   PRINT 'The average weight of the top 5 Touring-3000 bikes is ' + CAST(@AvgWeight AS VARCHAR(8)) + '.';  
END  
ELSE   
BEGIN  
SET @AvgWeight =   
        (SELECT AVG(Weight)  
         FROM Production.Product   
         WHERE Name LIKE 'Touring-3000%' );  
   PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS VARCHAR(8)) + '.' ;  
END ;  
GO
      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        4 years ago

        Hi Anonymous ,

         

        I see.

        You could use below structure do to the statement block:

        If  (condition A),

        then let {statement}

                 in (output)

        else  let {statement}

                 in (output)

         

        Below is the reference for let formular:

        https://docs.microsoft.com/en-us/powerquery-m/m-spec-let

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my reply as a solution!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Yes. Here's a silly example.

     

    let
    examples = { 1, 1.34, "foo", #date(2021, 1, 1), null },

    detectTypes = List.Transform(
        examples,
        (item) => 
            if item is text then "text"
            else if item is date then
                DateTime.LocalNow() - item
            else if item is null then "Bad News"
            else if item is number then item + 1.4
            else error Error.Record(
                "UnhandledTypeException",
                "There is no handler for this type",
                Value.Type(item)
            )
    ),
    #"Converted to Table" = Table.FromList(
        detectTypes, Splitter.SplitByNothing(),
        type table[Value = any], null, ExtraValues.Error)
in
    #"Converted to Table"

     

    If you have an example with names, I could give a better suggestion.

     

    To give you an idea, think about this. The dependency chain of the final step, "joinedNames" variable never refers to statement1 or statement2.

    This means statement1 an statement2 are never executed.

    let
    names = {"Ted", "Jack"},
    statement1 = somethingExpensive(),
    statement2 = somethingElseExpensive(),
    joinedNames = Text.Combine(names, ", " )
in
    joinedNames

    Also errors propagate untill you catch them. this means you don't have to not run certain logic if it's already thrown an exception.