Date format

[o59393]

Hi all

How can I convert a date in format "Apr 01 2019" to "April 2019" for example and using power query?

https://1drv.ms/u/s!ApgeWwGTKtFdhjOrLr9_z4xQWZca?e=KpC6mb 

Thanks.

[FrankAT]

Hi @o59393,

if you change the date in the desired format with Power Query the result will end up in a string. Sort order is alphabetical.

If you want to keep the date intact format it in model view:

1. Seelect model view.
2. Select date field in field list.
3. See figure. Sort order by calendar.

Regards FrankAT

[Greg_Deckler]

Perhaps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoUjAwVDAyMLRUitWJVvJKzIPwjQzAfLfUJAUDYwTfqzRHwdAUyo8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom Column" = Table.AddColumn(Source, "Custom", each Text.Combine({Date.ToText(Date.From([Column1]), "MMMM"), " ", Date.ToText(Date.From([Column1]), "yyyy")}), type text)
in
    #"Added Custom Column"

[o59393]

Hi 

Where do I have to paste that?

As an added custom column?

Thanks!

[zoupie]

sorry new to this formula builder.  I am trying yearmonth.  not working

yearmonth = FORMAT('vw_datamart_Activities'[dt_start],"YYYYMM")

This is more of the downsteam table

feel free to adjust our dashboard.  AppWorx_Azure

[Greg_Deckler]

That was the code behind the query. You would create a blank query and then paste that code into Advanced Editor. I think I created that code using Add Column From Examples.

[amitchandak · Accepted solution]

@o59393 ,

You have to go to Transform Data/Edit Query and use the Custom Column option.

In Dax , you can try like

 format(DATEVALUE("Apr 01 2019"),"MMMM-YYYY")

If it a static value you can use in measure too. Else in measure use some aggregation.