Merge columns based on several values of one

[mariaorriols]

Hi everyone, 

I have data since 2000, here I post a sample:

Month	Quarter	Year
January		2020
February		2020
March	Q1	2020
April		2020
May		2020
June	Q2	2020
July		2020
August		2020
September	Q3	2020
October		2020
November		2020
December	Q4	2020

I need to merge columns "Quarter" and "Year" but just when quarter equals Q1, Q2, Q3 or Q4. If quarter is empty, then the cell would remain empty:

Month	Quarter	Year	Year.Quarter
January		2020
February		2020
March	Q1	2020	2020/Q1
April		2020
May		2020
June	Q2	2020	2020/Q2
July		2020
August		2020
September	Q3	2020	2020/Q3
October		2020
November		2020
December	Q4	2020	2020/Q4

[Vijay_A_Verma · Accepted solution]

Use this in a custom column

= if [Quarter]="" or [Quarter]=null then null else [Quarter]&"/"&Text.From([Year])

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUQIiIwMjA6VYnWglt9SkIizCvolFyRlAgUBDZFHHgqLMHAyV6Hq9SvNSQVqNUAVz0NU5lqaXFpegCQanFpSk5ialFoFMMEaW8U8uyYeII4v65ZfBlCMLu6Qmw00xgUvEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Quarter = _t, Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Quarter", type text}, {"Year", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Year.Quarter", each if [Quarter]="" or [Quarter]=null then null else [Quarter]&"/"&Text.From([Year]))
in
    #"Added Custom"