Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.