Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Spigaw
Helper II
Helper II

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

2 ACCEPTED SOLUTIONS

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



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

10 REPLIES 10
Spigaw
Helper II
Helper II

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

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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.

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

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

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

Hello!

I have an update for this function. I am trying to add this parameter :

 

if [C Service] = "3894-4807" and [Code accord OTT] <> 11
then 0
else [parameters of the function already written]

I tried to add it at the beginning, in the middle and at the end of your expression, but to no avail... Any idea?

 

Thanks in advance for your precious help.

@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. 😉



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

That's what I thought after looking at it for ~ an hour, I finally added this control to another column and now use it as a secondary control. It works well, it's not the most elegant solution but it does the job and doesn't slow down my query, so I'm seeing it as resolved.

 

It was worth a try asking, and thank you again for saving me so much time with the first formula which was a nightmare!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors