Forum Discussion
Solved
IF statement
Hi I have a table with each month of the year and I want to split them up so they are in Periods. What is the best way to do this in the query editor? thanks in advance Period 1 Period 2 ...
Perhaps try:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU4rViVZyS00C076JRWDasaAIyq8E016leVA6ByJfmg6mg1MLwLR/cgmY9ssvA9MuqckQ9cSYHwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Date] = "Jan" or [Date]="Feb" or [Date]="Mar" then "Period 1" else if [Date]="Apr" or [Date]="May" or [Date]="Jun" then "Period 2" else if [Date]="Jul" or [Date]="Aug" then "Period 3" else "Period 4") in #"Added Custom"
Sorry the data has not displayed properly it should be
| Period 1 | Period 1 | Period 1 | Period 2 | Period 2 | Period 2 | Period 3 | Period 3 | Period 4 | Period 4 |
| Jan | Feb | March | April | May | June | July | August | September | October |
The month is coming from a date field
thanks
Perhaps try:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU4rViVZyS00C076JRWDasaAIyq8E016leVA6ByJfmg6mg1MLwLR/cgmY9ssvA9MuqckQ9cSYHwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Date] = "Jan" or [Date]="Feb" or [Date]="Mar" then "Period 1" else if [Date]="Apr" or [Date]="May" or [Date]="Jun" then "Period 2" else if [Date]="Jul" or [Date]="Aug" then "Period 3" else "Period 4")
in
#"Added Custom"