Power Query Add New Column > Date.ToText as MM/YYYY

[Anonymous]

Hi I am trying to create a new column in power query, so that I can create an effective relationship between our opporunities and sales targets, by owner and mm/yyyy

Currently our Close Dates are in dd/mm/yyyy format, where as our sales targets are always 01/mm/yyyy

So I would like to try and create the relationship, just using mm/yyyy

Currently I am trying this, which it doesn't like 😞

Date.ToText((AllOpportunities[Opportunity Close Date]),[Format="MM/YYYY"])&[Opportunity Owner]&[Opportunity Record Type])

I got the following working OK:

Date.ToText(AllOpportunities[Opportunity Close Date])&[Opportunity Owner]&[Opportunity Record Type]) which gives me: DD/MM/YYYYSteve SteversonB2B

I would like this to be MM/YYYYSteve SteversonB2B

[Rickmaurinus]

Hey Cryonix, 

There's in total 17 format strings 'pieces' that I associate with the Date.ToText function. And the fun is, you can put all of those pieces together in your own way to create a string. For example, the following string returns "Sun December 31, 2023"

Date.ToText( 
  #date( 2023, 12, 31 ), 
  [Format="ddd MMMM d, yyyy"] 
)

And this one gives us back 'december':

Date.ToText( 
  #date( 2023, 12, 31 ), 
  [Format="MMMM", Culture="nl-NL"] 
)

I've put all of them together in a clear table, which you can find here: 

https://powerquery.how/date-totext/

Hope that helps!

Rick

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[Anonymous]

worked like a charm, not sure why I didn't try that.  Thank you @amitchandak 

[amitchandak · Accepted solution]

@Anonymous , Based on what I got, you need

Date.ToText([Date],"MM/yyyy")

Please keep the case same