Column - Extract Month and Year

Anonymous — Tue, 30 Apr 2024 12:26:07 GMT

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

Apr-22

May-22

Jun-22

Jul-22

Aug-24

Please advise

Re: Column - Extract Month and Year

barritown — Tue, 30 Apr 2024 18:25:17 GMT

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

Re: Column - Extract Month and Year

manvishah17 — Wed, 01 May 2024 08:02:30 GMT

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.

topic Re: Column - Extract Month and Year in DAX Commands and Tips

Column - Extract Month and Year

Re: Column - Extract Month and Year

Re: Column - Extract Month and Year