Forum Discussion
Solved
Merge columns based on values
Hi everyone
My data looks like (all of them are text type):
| Month1 | Month | Year |
| 01 | January | 2022 |
| 02 | February | 2022 |
| 03 | March | 2022 |
| 04 | April | 2022 |
| 05 | May | 2022 |
I am only showing these months of 2022, but I have data since 2005.
I want to create a new column, Month.Year, that combines both "Month" and "Year" if Month equals "Gener". If else it should state just Month. So it would look like:
| Month1 | Month | Year | Month.Year |
| 01 | January | 2022 | January 2022 |
| 02 | February | 2022 | February |
| 03 | March | 2022 | March |
| 04 | April | 2022 | April |
| 05 | May | 2022 | May |
| 06 | June | 2022 | June |
| 07 | July | 2022 | July |
| 08 | August | 2022 | August |
| 09 | September | 2022 | September |
| 10 | October | 2022 | October |
| 11 | November | 2022 | November |
| 12 | December | 2022 | December |
| 01 | January | 2021 | January 2021 |
Use following formula in a custom column
= if Number.From([Month1])=1 then [Month]&" "&Text.From([Year]) else [Month]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("i45WMlTSUfJKzCtNLKoEsowMjIyUYnWilYyAHLfUpCJ0cWMgxzexKDkDWdAEyHEsKMrMQRY0BatEaI4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month1 = _t, Month = _t, Year = _t]), #"Added Custom" = Table.AddColumn(Source, "Month.Year", each if Number.From([Month1])=1 then [Month]&" "&Text.From([Year]) else [Month]) in #"Added Custom"
1 Reply
Use following formula in a custom column
= if Number.From([Month1])=1 then [Month]&" "&Text.From([Year]) else [Month]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("i45WMlTSUfJKzCtNLKoEsowMjIyUYnWilYyAHLfUpCJ0cWMgxzexKDkDWdAEyHEsKMrMQRY0BatEaI4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month1 = _t, Month = _t, Year = _t]), #"Added Custom" = Table.AddColumn(Source, "Month.Year", each if Number.From([Month1])=1 then [Month]&" "&Text.From([Year]) else [Month]) in #"Added Custom"