IF AND statement Power Query

[Anonymous]

Hi. I'm trying to add new custom column using IF AND statement. If "Aging" is greater than 60, and "Aging" is less than 365, give "Total PD".

If(and(Aging>60,Aging<365),Total PD,0)

It seems like it's giving me "Token Literal Expected" error when I try to put above statement.

What am I doing wrong?

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft]

Hi @Anonymous ,

In Power Query Editor, we can also use the gui to add such a column.

The M Query generated by the GUI

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZQ0lEyNDBQitWJVjJH5hgjeLEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Aging = _t, #"Total PD" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Aging", Int64.Type}, {"Total PD", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "NewColumn", each if [Aging] <= 60 then 0 else if [Aging] >= 365 then 0 else [Total PD]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "NewColumn2", each if [Aging] <= 60 then 0 else if [Aging] < 365 then [Total PD] else 0)
in
    #"Added Conditional Column1"

Although it can not add AND condition, but it can works in most condition.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rsimonsen]

ahjlim32

If you are doing this with Power Query Editor then Gordonlilj solution is the way to go. If you are using the add new column under modeling this should work.

IfTest = if(and([Aging]>60,[Aging]<365),[Total PD],0)

[Nathaniel_C]

Hi @Anonymous ,

Try this:

 Table.AddColumn(#"Changed Type", "Custom", each if [Aging] > 60 and [Aging] <365 then [Total PD] else 0)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Gordonlilj]

Hi,

Are you creating the column in the power query editor? if so the code you're using will not work as it's using DAX (power query uses M). The code should work if you create a calculated column instead.

If you want to create it using a custom column in power query then the code below should work

if [Aging] >60 and [Aging]<365 then [Total PD] else 0