cancel
Showing results for
Did you mean:
Frequent Visitor

## 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...

1 ACCEPTED SOLUTION
Super User III

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 =
#"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
),
in

You can see that the final column returns TRUE because the New Value column matches the Used column.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
7 REPLIES 7
Frequent Visitor

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!

Super User IV

@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 1``````

That 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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Super User III

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 1``````

But it has no syntax errors. Still, it may not be doing what you want.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Frequent Visitor

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.

Super User III

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 =
#"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
),
in

You can see that the final column returns TRUE because the New Value column matches the Used column.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Frequent Visitor

Firstly, I want to thank you for the time you spent on this problem, and for the quality of your work.

I was especially amazed by two things:

• Defining variables, I didn't know it was possible with M and it will save me an awful lot of time for further projects;
• Using Number.IntegerDivide to extrapolate year and month from the period, which is as simple as it is elegant.

I'm implementing your work in my files right away, I can't thank you enough! Have a wonderful day

Super User III

Excellent @Spigaw - glad I was able to help!

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.