IF with multiple statements in M language

[Anonymous]

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, delimited by a begin ... end construct or similar construct?
Many thanks

[ImkeF]

Hi @Anonymous ,
you can compare the let-in-construct with a Begin-End-syntax like you've described.
Just have in mind that PQ is lazy, so it will only evaluate/execute steps that are necessary to evaluate the "in"-result.
So if every step shall be a single execution of something (which I'm having difficulties to imagine, but your question is fairly generic, so I'm not questioning that), then make sure that its result (or a reference to it) is referenced by the final result.

In the following valid M-code the "statement3" will not be executed, because it is not needed to evaluate the final result:

if 1 = 1                // Condition
then        
let                     // Begin
Statement1 = 10, 
Statement2 = 20,
Statement3 = 40
in
Statement1 + Statement2 // End
else 
0

If your statements shall do something more interesting like calling APIs for example, make sure that you reference their results somehow in what the "in" returns. 

[mussaenda]

Hi @Anonymous ,

Based on my understanding on your question, it is possible to do nested if else statements on Power Query.

Hope this helps.

[Anonymous]

As the great Ben Gribaudo put it, the only control structures in M are if/then/else. I your case, you probably want if/then/else/if. Basically, multiple conditions outcomes need to be chained together using else if, and conditions can use and/or logic:

if [Widget] = 4 and [Agent] = "Sam" then "Sam's Sale" else if [Widget] = 2 or [Agent].<> "Bob" then "Other" else "No one"

--Nate

[Anonymous]

I'm thinking to something like this:

if <condition>

<begin or other start construct>

<statement 1>

<statement 2>

...

<statement n>

<end or other end construct>

Thanks

[v-kelly-msft]

Hi @Anonymous ,

Is your issue solved now?

Best Regards,
Kelly

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

[Anonymous]

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

[Anonymous]

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.

[v-kelly-msft]

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]

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]

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!

[v-kelly-msft]

Hi  @Anonymous ,

Could you pls make your requirement detailed?Such as providing some sample data with expected output?

Best Regards,
Kelly

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

[v-kelly-msft]

Hi @Anonymous ,

You could use "if" with "else... then" for different senarios or use "if" with "and/or" with multiple conditions,check below reference:

https://gorilla.bi/power-query/if-function-in-power-query/

Best Regards,
Kelly

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

[Greg_Deckler]

@Anonymous Not sure what you mean, like if then else if then else if then else or multiple logical criteria?

There is no switch or case statement in M