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 | Period 3 | Period 4 | Period 1 | |||||||||
| Jan-19 | Feb-19 | Mar-19 | Apr-19 | May-19 | Jun-19 | Jul-19 | Aug-19 | Sep-19 | Oct-19 | Nov-19 | Dec-19 | Jan-20 | Feb-20 |
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"
4 Replies
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"
Are those months and years text or do you convert them to dates? And you want this in the query editor, not DAX, correct?
They come from the source as dates, in the query editor or as DAX which ever is easiest to do.
thanks for your help
