March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Im' trying to do adapt a Excel Function, to a Query in Power Query, but I'm having a lot of trouble.
I convert the date to "RYYMMM" where "YY" are the last two digits of the month and "MMM" is the abreviation in uppercase of the Month. But the problem is when I try to put the criteria, that this format should be only applied if the date in question is before TODAY()-2 months.
The Excel Formula is the following:
=IF(OR([@[Data de Distribuição]]="";[@[Data de Distribuição]]="MODEL");"";IF(DATE(YEAR([@[Data de Distribuição]]);MONTH([@[Data de Distribuição]]);1)<=DATE(YEAR(TODAY());MONTH(TODAY())-2;1);CONCATENATE("R";UPPER(TEXT(DATE(YEAR([@[Data de Distribuição]]);MONTH([@[Data de Distribuição]]);1);"AAMMM")));""))
In Power query, I'm typing this on the custom column:
if #date(Date.Year([DataPrazoCumprido]), Date.Month([DataPrazoCumprido],Date.StartOfMonth([DataPrazoCumprido]))) <= Date.AddMonths(#date(DateTime.LocalNow()), 2) then "R" & Text.End(Date.ToText(DateTime.Date([DataPrazoCumprido])),2) & Text.Upper(Date.ToText([DataPrazoCumprido],"MMM" ))
else null
The part after "then" works just fine (as shown on the highlited image), but when I try to do it with the criteria, it returns this error:
Expression.Error: 2 arguments were passed to a function which expects 1.
Details:
Pattern=
Arguments=[List]
Can anyone help me please?
Solved! Go to Solution.
Hi @Pererelson ,
I think that you may want to use #date(year,month,day). Please refer to the following codes:
#date(Date.Year([DataPrazoCumprido]), Date.Month([DataPrazoCumprido]),Date.Day(Date.StartOfMonth([DataPrazoCumprido])))
Hi @Pererelson ,
I think that you may want to use #date(year,month,day). Please refer to the following codes:
#date(Date.Year([DataPrazoCumprido]), Date.Month([DataPrazoCumprido]),Date.Day(Date.StartOfMonth([DataPrazoCumprido])))
Missing Bracket?
Date.Month([DataPrazoCumprido],
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
21 | |
16 | |
13 | |
12 | |
9 |
User | Count |
---|---|
34 | |
31 | |
20 | |
19 | |
17 |