Power Query: Column to return specific values

[Anonymous]

HI PBI community members, 

I have a column titled  "Month" that has the following values:

• March
• June
• September
• December

In Power Query, I would like to make a new column that returns the following: when Month column is March or June the new column value is -1; and when Month column is September or December new column value is 1.

This is to work around some calander year values and turn them into fiscal years. 

Cheers,

Brent

[Vera_33 · Accepted solution]

Hi @Anonymous 

Month column contains month only? if statement will work, here is one way

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8lQitUBshKLkiEsr9K8VDAjOLWgJDU3KbUIzHNJTUZwfBMrlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "newColumn", each if List.Contains({"March", "June"},[Month]) then -1 
else if List.Contains({"September","December"},[Month]) then 1 
else [Month])
in
    #"Added Custom"