IF then with multiple lines

[PhilippeMuniesa]

Hello

Hello, I stumble on the syntax of an If then else with several instructions, what I want to do:

if condition then

     line instructions1,

     line instructions2,

     line instructions3

else

    Instruction line 4

concretely, previous step...

Supprime_Lignes_Sans_CPart = Table.SelectRows(Groupe_compte_Compte_Part, each ([CompAuxNum] <> "")),

follow-up

Si_Pas_CP = if Table.RowCount(Supprime_Lignes_Sans_CPart) = 0 then
     Groupe_compte_Compte_Part = Table.Group(Source, {"CompAuxNum", "CompAuxLib", "CompteNum", "CompteLib"}, {{"Debit", each List.Sum([Debit]), type nullable number}, {"Credit", each List.Sum([Credit]), type nullable number}}),
    CompteNum_texte_pour_filtre = Table.TransformColumnTypes(Groupe_compte_Compte_Part ,{{"CompteNum", type text}}),
     BufferList = List.Buffer(CP_Dans_PCG),
     LenRacine = Text.Length(CP_Dans_PCG {0}),
    Ajoute_Col_racine = Table.AddColumn(CompteNum_texte_pour_filtre, "Racine_CompteNum", each Text.Start([CompteNum],LenRacine )),

    Filtre_Selon_Racines_Selectionnes = Table.SelectRows (Ajoute_Col_racine, each List.Contains (BufferList , [Racine_CompteNum])),
    Compte_Num_Re_Type_Entier = Table.TransformColumnTypes(Filtre_Selon_Racines_Selectionnes,{{"CompteNum", Int64.Type}}),

    Tri_Compte_Num = Table.Sort(Compte_Num_Re_Type_Entier,{{"CompteNum", Order.Ascending}}),

else

   null),

and still a token problem expected

Thank for your help

Philippe Muniesa

[AlB · Accepted solution]

Hi @PhilippeMuniesa 

Try this. You might need to add a comma at the very end depending on what comes next.

Si_Pas_CP = if Table.RowCount(Supprime_Lignes_Sans_CPart) = 0 then
    let 
        Groupe_compte_Compte_Part = Table.Group(Source, {"CompAuxNum", "CompAuxLib", "CompteNum", "CompteLib"}, {{"Debit", each List.Sum([Debit]), type nullable number}, {"Credit", each List.Sum([Credit]), type nullable number}}),
        CompteNum_texte_pour_filtre = Table.TransformColumnTypes(Groupe_compte_Compte_Part ,{{"CompteNum", type text}}),
        BufferList = List.Buffer(CP_Dans_PCG),
        LenRacine = Text.Length(CP_Dans_PCG {0}),
        Ajoute_Col_racine = Table.AddColumn(CompteNum_texte_pour_filtre, "Racine_CompteNum", each Text.Start([CompteNum],LenRacine )),
        Filtre_Selon_Racines_Selectionnes = Table.SelectRows (Ajoute_Col_racine, each List.Contains (BufferList , [Racine_CompteNum])),
        Compte_Num_Re_Type_Entier = Table.TransformColumnTypes(Filtre_Selon_Racines_Selectionnes,{{"CompteNum", Int64.Type}}),
        Tri_Compte_Num = Table.Sort(Compte_Num_Re_Type_Entier,{{"CompteNum", Order.Ascending}})
    in 
        Tri_Compte_Num
else
    null

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

Hi @PhilippeMuniesa 

Try this. You might need to add a comma at the very end depending on what comes next.

Si_Pas_CP = if Table.RowCount(Supprime_Lignes_Sans_CPart) = 0 then
    let 
        Groupe_compte_Compte_Part = Table.Group(Source, {"CompAuxNum", "CompAuxLib", "CompteNum", "CompteLib"}, {{"Debit", each List.Sum([Debit]), type nullable number}, {"Credit", each List.Sum([Credit]), type nullable number}}),
        CompteNum_texte_pour_filtre = Table.TransformColumnTypes(Groupe_compte_Compte_Part ,{{"CompteNum", type text}}),
        BufferList = List.Buffer(CP_Dans_PCG),
        LenRacine = Text.Length(CP_Dans_PCG {0}),
        Ajoute_Col_racine = Table.AddColumn(CompteNum_texte_pour_filtre, "Racine_CompteNum", each Text.Start([CompteNum],LenRacine )),
        Filtre_Selon_Racines_Selectionnes = Table.SelectRows (Ajoute_Col_racine, each List.Contains (BufferList , [Racine_CompteNum])),
        Compte_Num_Re_Type_Entier = Table.TransformColumnTypes(Filtre_Selon_Racines_Selectionnes,{{"CompteNum", Int64.Type}}),
        Tri_Compte_Num = Table.Sort(Compte_Num_Re_Type_Entier,{{"CompteNum", Order.Ascending}})
    in 
        Tri_Compte_Num
else
    null

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[PhilippeMuniesa]

Hello super users, 

sorry to come back on the subject but I always have a problem of syntax and comma,

I attach the example that your had provided me and that I thought I could solve my problem, but that always makes appear a problem of comma.

=======

Let

Source = Ouvre_FEC1,
Groupe_compte_Compte_Part = Table.Group(Source, {"CompAuxNum", "CompAuxLib", "CompteNum", "CompteLib"}, {{"Debit", each List.Sum([Debit]), type nullable number}, {"Credit", each List.Sum([Credit]), type nullable number}}),
Supprime_Lignes_Sans_CPart = Table.SelectRows(Groupe_compte_Compte_Part, each ([CompAuxNum] <> "")),


Si_Pas_CP = if Table.RowCount(Supprime_Lignes_Sans_CPart) = 0 then
let
Groupe_compte_Compte_Part = Table.Group(Source, {"CompAuxNum", "CompAuxLib", "CompteNum", "CompteLib"}, {{"Debit", each List.Sum([Debit]), type nullable number}, {"Credit", each List.Sum([Credit]), type nullable number}}),
CompteNum_texte_pour_filtre = Table.TransformColumnTypes(Groupe_compte_Compte_Part ,{{"CompteNum", type text}}),
BufferList = List.Buffer(CP_Dans_PCG),
LenRacine = Text.Length(CP_Dans_PCG {0}),
Ajoute_Col_racine = Table.AddColumn(CompteNum_texte_pour_filtre, "Racine_CompteNum", each Text.Start([CompteNum],LenRacine )),
Filtre_Selon_Racines_Selectionnes = Table.SelectRows (Ajoute_Col_racine, each List.Contains (BufferList , [Racine_CompteNum])),
Compte_Num_Re_Type_Entier = Table.TransformColumnTypes(Filtre_Selon_Racines_Selectionnes,{{"CompteNum", Int64.Type}}),
Tri_Compte_Num = Table.Sort(Compte_Num_Re_Type_Entier,{{"CompteNum", Order.Ascending}})
in
Tri_Compte_Num
else

null

=================

I also attach the entire structure of the code of my query that would present the following features:

Let

     Source,

     Stage1,

     Stage2,

           IF statement then

               Let

                  Step3,

                  Step4,

                  Result_desired

              In

                  Result_desired

          Else

             Let

                  Step1b,

                 Step2b,

                  Step3b,

                  Result_desired   // Note that then name of this step is the same than if the IF condition is yes

             in

                   Result_Desired  // whatever the result of the conditional test name of the result is the same in order to                                                     continue the treatment on the basis of the IF result ... THEN, here the final result

in

Results_Desired  

========== and the real request ====

Let
Source = Ouvre_FEC1,
Groupe_compte_Compte_Part = Table.Group(Source, {"CompAuxNum", "CompAuxLib", "CompteNum", "CompteLib"}, {{"Debit", each List.Sum([Debit]), type nullable number}, {"Credit", each List.Sum([Credit]), type nullable number}}),
Supprime_Lignes_Sans_CPart = Table.SelectRows(Groupe_compte_Compte_Part, each ([CompAuxNum] <> "")),

// If Condition
Si_Pas_CP = if Table.RowCount(Supprime_Lignes_Sans_CPart) = 0 then

Let
Groupe_compte_Compte_Part = Table.Group(Source, {"CompAuxNum", "CompAuxLib", "CompteNum", "CompteLib"}, {{"Debit", each List.Sum([Debit]), type nullable number}, {"Credit", each List.Sum([Credit]), type nullable number}}),
CompteNum_texte_pour_filtre = Table.TransformColumnTypes(Groupe_compte_Compte_Part ,{{"CompteNum", type text}}),
BufferList = List.Buffer(CP_Dans_PCG),
LenRacine = Text.Length(CP_Dans_PCG {0}),
Ajoute_Col_racine = Table.AddColumn(CompteNum_texte_pour_filtre, "Racine_CompteNum", each Text.Start([CompteNum],LenRacine )),

Filtre_Selon_Racines_Selectionnes = Table.SelectRows (Ajoute_Col_racine, each List.Contains (BufferList , [Racine_CompteNum])),
Compte_Num_Re_Type_Entier = Table.TransformColumnTypes(Filtre_Selon_Racines_Selectionnes,{{"CompteNum", Int64.Type}}),
Result = Table.Sort(Compte_Num_Re_Type_Entier,{{"CompteNum", Order.Ascending}})

In
Result
// If Not condition
Else

Let

Tri_Croissant = Table.Sort(Supprime_Lignes_Sans_CPart,{{"CompteNum", Order.Ascending}}),
Col_Solde = Table.AddColumn(Tri_Croissant, "Solde", each [Debit]-[Credit]),
Result = Table.TransformColumnTypes(Col_Solde,{{"Solde", type number}})

In

Result

=======

Thank you for explaining to me what gets stuck and how to get me out of this impasse

Philippe Muniesa

In

Result

============

[PhilippeMuniesa · Accepted solution]

Hello again,

I answer myself to avoid a waste of time to the super users..

After various attempts I just found the syntactic solution

let

       Source,

       step1,

       step2,

       Conditional_Req =  if statement then    // Conditional_Req result is result1 or Result2 at the end of each occurrence of                                      conditional treatment (in)

            let

                 step3,

                 Step4,

                 Step5,

                Result1

            in 

               Result1

       else

            let

               step1b,

                Step2b,

                Result2

            in

                 Result2,  // if treatments continue, a comma is required at this level, whereas the first in before the                                                other   did not require a comma

     Step3c (based on Conditional_Req),  //resume treatments from the name of the conditional requete

     Step4c,

     FinalResult

in

FinalResult

I hope to have contributed to this forum by providing a solution to other users or contributors, and to replace the Super Users that set me on the path, and forced me to think about finding the solution

Philippe Muniesa

[PhilippeMuniesa]

Super,

thanks a lot

it s seems so simple when you Know.... 👍

philippe Muniesa

[watkinnc]

+1 on the solution above.