Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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...
Solved! Go to Solution.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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. 😉
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou'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!
@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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello 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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingFirstly, 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:
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!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello!
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. 😉
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThat'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!