Forum Discussion
Converting a complex Excel formula to M language
Hello there,
I am a bit stuck with a formula that is 10 years old, and the person who wrote it is long gone from the company.
Here it is:
SI([@Période]="";"";SI([@[Année de départ]]="";SI(OU([@[Année d''arrivée]]<>CNUM(GAUCHE([@Période];4));[@[Mois d''arrivée]]<>CNUM(DROITE([@Période];2)));SI(ET([@[Type de contrat]]="Expatriate";[@Enregistré]=1);0;1);SI([@[Jour d''arrivée]]>15;0;SI(ET([@[Type de contrat]]="Expatriate";[@Enregistré]=1);0;1)));SI(DATE([@[Année de départ]];[@[Mois de départ]];[@[Jour de départ]])-DATE([@[Année d''arrivée]];[@[Mois d''arrivée]];[@[Jour d''arrivée]])<15;0;SI([@[Mois de départ]]<>CNUM(DROITE([@Période];2));0;SI([@[Jour de départ]]<15;0;SI(ET([@[Type de contrat]]="Expatriate";[@Enregistré]=1);0;1))))))
I tried converting it myself (without this bit : SI(ET([@[Type de contrat]]="Expatriate";[@Enregistré]=1);0;1) as it is redundant and evaluated in another column), but I always have errors here and there. Below is what I did (I replaced the data with months and years with Date.From as it is easier than using 3 columns):
if [D Sortie société] <> null
then if ([D Sortie société] >=
Date.From(#date(Date.Year([Date]),
Date.Month([Date]), 15)))
then 1
else 0
else if ([D Entrée société] >=
Date.From(#date(Date.Year([Date]),
Date.Month([Date]), 15)))
then 0
else 1
Thanks in advance for any help...
If I had more time, I'd optimize the heck out of this because the Excel formula in the USED field is a bit redundant in some places, plus it cannot do what PQ can do. However, I pretty much replicated it, and it works. Same results as Used.
let Source = Excel.CurrentWorkbook(), Tableau1 = Source{[Name="Tableau1"]}[Content], #"Type modifié" = Table.TransformColumnTypes(Tableau1,{{"Date", type date}, {"Entry date", type date}, {"Leaving date", type date}}), #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "New used", each if [Leaving date] = null and [Entry date] <= Date.From(#date(Date.Year([Date]), Date.Month([Date]), 15)) then 1 else if [Leaving date] <> null and [Entry date] >= Date.From(#date(Date.Year([Date]), Date.Month([Date]), 15)) then 0 else if [Leaving date] <> null and [Leaving date] >= Date.From(#date(Date.Year([Date]), Date.Month([Date]), 15)) then 1 else 0), NewValue = Table.AddColumn( #"Personnalisée ajoutée", "NewValue", each let varPeriodYear = Number.IntegerDivide([Period],100), varPeriodMonth = [Period] - varPeriodYear * 100 in if [Departure year] = "" then if [Arrival year] <> varPeriodYear or [Arrival month] <> varPeriodMonth then if [Contract type] = "Expatriate" and [Registered] = 1 then 0 else 1 else if [Arrival day] > 15 then 0 else if [Contract type] = "Expatriate" and [Registered] = 1 then 0 else 1 else if Duration.TotalDays(#date([Departure year], [Departure month], [Departure day]) - #date([Arrival year], [Arrival month], [Arrival day])) < 15 then 0 else if [Departure month] <> varPeriodMonth then 0 else if [Departure day] < 15 then 0 else if [Contract type] = "Expatriate" and [Registered] = 1 then 0 else 1 ), #"Added Custom" = Table.AddColumn(NewValue, "Custom", each [NewValue] = [Used]) in #"Added Custom"You can see that the final column returns TRUE because the New Value column matches the Used column.
Spigaw that is a massive "it depends" and would require untangling the original code above and inserting it in the right place. As I said in my original reply, that was not optimized because the excel formula had some nested IF() statements that were redundant, but it would take time to map it out and ensure it was working right. It was spaghetti code to begin with, and this makes it worse. 😉
10 Replies
Spigaw Well I am no M genius but I know one thing that is wrong:
if [D Sortie société] <> null then if ([D Sortie société] >= Date.From(#date(Date.Year([Date]), Date.Month([Date]), 15))) then 1 else 0 else if ([D Entrée société] >= Date.From(#date(Date.Year([Date]), Date.Month([Date]), 15))) then 0 else 1That else 0 followed by an else if is not correct. You can'd do that, you can do if then else or if then else if, has to be nested just like Excel's IF statement.
Actual M geniuses = ImkeF edhans
Yeah, from a syntax standpoint that is wrong. This should work, but without the data I cannot test.
if [D Sortie société] <> null then 0 else if ([D Sortie société] >= Date.From(#date(Date.Year([Date]), Date.Month([Date]), 15))) then 1 else if ([D Entrée société] >= Date.From(#date(Date.Year([Date]), Date.Month([Date]), 15))) then 0 else 1But it has no syntax errors. Still, it may not be doing what you want.
Hello again,
I tried something new but I still don't get all the correct results I am searching. As you said that you don't have the data, you will find it attached here with no sensible information (and roughly translated from French to English).
What I got until now:
- The column P ("Used") is the one I am trying to reproduce in Query, which gets data passed to it by columns S ("Entry date") and T ("Leaving date")
- Some cases don't match with my M formula (the last one in the table is a good example)
- The M formula I used is in "Requête1" in Query, called "New used"
- The sheet "Pivot table" compares the results from Used and New used; you'll see that some don't match as of now.
Thanks again for your precious help and time.
You're right, I have two many arguments in my first if statement.
I will try to add an and argument for the first part of the expression and use what you proposed, I'll be right back to tell you if it worked.
Thanks to you two for answering so quick!
