Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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"
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"
Check out the July 2025 Power BI update to learn about new features.