Column - Extract Month and Year

[GallopPBI]

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

[manvishah17]

HI @GallopPBI , 
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.

[barritown]

Hi @GallopPBI,

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