Forum Discussion

mariaorriols's avatar
mariaorriols
Regular Visitor
4 years ago
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   ...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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"