cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Spigaw
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
edhans
Super User III
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 = 
        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.



Did I answer your question? Mark my post as a solution!
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

View solution in original post

7 REPLIES 7
Spigaw
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!

Greg_Deckler
Super User IV
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 

 


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

@ me in replies or I'll lose your thread!!!

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




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

Proud to be a Super User!




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.

edhans_0-1627592578297.png

 



Did I answer your question? Mark my post as a solution!
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
Spigaw
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.

edhans
Super User III
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 = 
        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.



Did I answer your question? Mark my post as a solution!
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

View solution in original post

Spigaw
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

edhans
Super User III
Super User III

Excellent @Spigaw - glad I was able to help!



Did I answer your question? Mark my post as a solution!
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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Top Kudoed Authors