Language M - if statement on several lines

[synetiax]

Bonjour,
Hi,
J’ai créer un fichier pour mieux exprimer mon besoin.
I create a zip file to explain my need.

Vous trouverez 3 fichiers dans le zip (piton de droite sur le fichier zip> Extraire tout):
You found 3 files in the zip.
Les fichier “C.xlsx” et “P.xlsx” qui contiennent les données et le fichier “Test.xlsm” qui contient le bug à résoudre.
The files "C.xlsx" and "P.xlsx" contains the data and the file "Test.xlsm" the bug.
Dans le fichier “Test.xlsm” vous trouverez 4 requêtes.
In the file "Test.xlsm", you will find 4 queries.
Les requêtes “ReqC” et “ReqP” transforment les fichiers sources “C.xlsx” et “P.xlsx”.
The queries “ReqC” and “ReqP” transform the source data “C.xlsx” and “P.xlsx”.

La Requete1 affiche la “ReqC” ou la “ReqP” en fonction du chiffre inscrit dans la plage nommée PN_Data de l’onglet Excel “test1”.
The query "Requete1" affiche the queries “ReqC” or the “ReqP” depending on the name PN_Data in excel sheet "test1".
Le bug se trouve dans la requête “Requete2 dynamique” où j’essaie de tout faire dans une seule requête.
The bug is on the query “Requete2 dynamique” where I try to do the same thing of "Requete1" but in 1 query.
Les fichiers sources ne seront jamais disponible en même temps. Lorsque le chiffre 5 est inscrit en PN_Data, seule le fichier “C” existera. Lorsque le chiffre inscrit en PN_Data sera différent de 5, seul le fichier P.xlsx sera disponible.
The sources files will be not exists in the same time. When the number 5 will be write in the name "PN_Data", only the file "C.xlsx" will exist. Same thing for the "P.xlsx" ...
C'est surement une virgule mal placée ou un truc dans le genre.
It's probably a comma on a wrong place or something like that.
Merci pour votre aide!
Thank you for you help
Synetiax

 

[AliGW]

On 'parle' plutôt en anglais ici - veuillez enlever le texte en français, car votre message est vraiment difficile à lire. Merci.

 

[synetiax]

Hi,

I create a zip file to explain my need.

You found 3 files in the zip.

The files "C.xlsx" and "P.xlsx" contains the data and the file "Test.xlsm" the bug.

In the file "Test.xlsm", you will find 4 queries.

The queries “ReqC” and “ReqP” transform the source data “C.xlsx” and “P.xlsx”.

The query "Requete1" display the queries “ReqC” or the “ReqP” depending on the name PN_Data in excel sheet "test1".

The bug is on the query “Requete2 dynamique” where I try to do the same thing of "Requete1" but in 1 query.

The sources files will be not exists in the same time. When the number 5 will be write in the name "PN_Data", only the file "C.xlsx" will exist. Same thing for the "P.xlsx" ...

It's probably a comma on a wrong place or something like that.

Thank you for you help

Synetiax

 

[Ken Puls]

Salut Synetiax,

My apologies, but my command of French is not sufficient to explain this.

The issue is that you've got a bunch of steps within each "if/then/else" clause. I initially just tried to wrap them in parenthesis, but that isn't sufficient in this case, as there are too many. The solution is to wrap the steps within the "then" and "else" branches in a let/in statement.

I've attached a copy of the workbook here. (Be aware that the code is indented, as it helps make it easier for me to troubleshoot.)

 

[p45cal]

Before I looked at Ken's solution I initially solved it in the same way (Requête2 dynamique). It surprised me that it allowed, for example, Source to be defined twice in the same query - so clearly being in a different let/in section means it's not in the same scope so it is allowed. Something to remember.

I also experimented with a different approach, that is not using the same name twice and putting the whole lot in one let/in section (Requête2 dynamique (2)). Because M is a functional language I expect M's lazy execution will mean it's just as efficient. It has one additional feature, it lets you see all the steps in the GUI which might let you edit the code more easily.

 

[Ken Puls]

You can actually call a query Source as well, although I wouldn't say it's a good practice. When you look at a nested "let x in x" block, those are essentially nested formulas, so re-use of terms is permitted.