Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

IF AND statement Power Query

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...
Need Help
Show and Tell
Tips and Tricks
v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

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.