Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
HI PBI community members,
I have a column titled "Month" that has the following values:
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
Solved! Go to 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"
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"