Convert Text date to date format

[Anonymous]

I have a text date field in the format of YYYYMMDD. I need to be able to reference just the YYYYMM in a slicer. How can I get this into a slicer?

Thanks

[Kaviraj11]

Hi,

There's a couple of ways you can create a format and use in slicer

1. Calculated Column in Date Table:

   • If you have a date table, create a calculated column with the following formula:

     Date_New = FORMAT('my_data'[Date], "YYYYMM")

2. M Query in Query Editor:

   • In the Query Editor, write a single M statement to convert YYYYMMDD to a proper date format:

     Date.From(Text.From([Date], "en-US"))

3. Concatenation for Month Slicer:

   • If you want to use the YYYYMM as a slicer, you can create a calculated column for the month:

     Yearmonth = CONCATENATE(YEAR([Date]), MONTH([Date]))

     Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Ashish_Mathur]

Hi,

This custom column in Power Query will work

Date.From(Text.From([DateKey], "en-US")  

[Anonymous]

Ashish, Could you provide an example. I'm not sure I understand the syntax.

Thanks

[Ashish_Mathur]

Hi,

That code simply has to be pasted in the Custom Column window of Power Query.

[Irwan]

hello @Anonymous 

you need to extract year, month, and day value from that string to create date format YYYY-MM-DD.

this is one way of many ways.

Both DAX and Power Query work same (extract string number).

if you want to use DAX.

Date format DAX =
var _year = LEFT('Table'[Column1],4) //extract year number
var _day = RIGHT('Table'[Column1],2) //extract day number
var _month = RIGHT('Table'[Column1],4)-RIGHT('Table'[Column1],2)
var _monthnum = SUBSTITUTE(_month,RIGHT(_month,2),"") //extract month number
Return
DATEVALUE(CONCATENATE(CONCATENATE(_year&"-",_monthnum&"-"),_day))

if you want to use Power Query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjEwMzRUitWBcAwNDU2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Inserted First Characters" = Table.AddColumn(#"Changed Type", "First Characters", each Text.Start(Text.From([Column1], "en-ID"), 4), type text),
#"Inserted Text Range" = Table.AddColumn(#"Inserted First Characters", "Text Range", each Text.Middle(Text.From([Column1], "en-ID"), 4, 6), type text),
#"Extracted First Characters" = Table.TransformColumns(#"Inserted Text Range", {{"Text Range", each Text.Start(_, 2), type text}}),
#"Inserted Last Characters" = Table.AddColumn(#"Extracted First Characters", "Last Characters", each Text.End(Text.From([Column1], "en-ID"), 2), type text),
#"Merged Columns" = Table.CombineColumns(#"Inserted Last Characters",{"First Characters", "Text Range", "Last Characters"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Date", "Date Power Query"}})
in
#"Renamed Columns"

dont forget to change data type into date after using DAX or Power Query since both result still in text.

Hope this will help you.

Thank you.