Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Column - Extract Month and Year

Hi Team,

 

I have the below data in a column :

 

Column
abc_tef_ghit_04_2022_1
abc_tef_ghit_05_2022_1
abc_tef_ghit_06_2022_1
abc_tef_ghit_07_2022_1
abc_tef_ghit_08_2022_1

 

Required output :

 

Column

Column
Apr-22
May-22
Jun-22
Jul-22

Aug-24

 

Please advise

"Calculated Column"

2 Replies

  • barritown's avatar
    barritown
    Solution Sage
    2 years ago

    Hi Anonymous,

    If the prefix and suffix are fixed, you can do something like this in DAX:

    In plain text for convenience:

    Column as Date = DATEVALUE ( SUBSTITUTE ( LEFT ( SUBSTITUTE ( [Column], "abc_tef_ghit_", "" ), 7 ), "_", "/" ) )

    Best Regards,

    Alexander

    My YouTube vlog in English

    My YouTube vlog in Russian

     

     

  • manvishah17's avatar
    manvishah17
    Solution Supplier
    2 years ago

    HI Anonymous , 
    You can easily transform your column in Power Query .
    USE THIS, Extract the text you want , replace _ to - and then change data type to text .
    After that insert year , month column and then merge them. =
    Text.Middle([Column], 'Start index','end index' )

     

     

     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKji9JTYtPz8gsiTcwiTcyMDKKN1SK1UGXMsUtZYZbyhy3lAWylIJSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Middle([Column1], 13, 7)),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","_","-",Replacer.ReplaceText,{"Custom"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Custom", type date}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type1", "Month Name", each Date.MonthName([Custom]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Custom]), Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Year", {{"Year", type text}}, "en-IN"),{"Month Name",  "Year"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")

     

     

    If this post helps , ACCEPT IT AS SOLUTION , so others can find it easily.