Power Query Date.ToText Format Specification

[LockeGarmin]

The Power Query page on Date.ToText (https://msdn.microsoft.com/en-us/library/mt253497.aspx) shows that you can use an optional parameter 'format' so you can represent dates as "yyyy/MM/dd". I've been trying to figure out how to get the quarter by using "QQ" or "qq" but neither of these have been working. This page tries to refer you to the "Library specification document" which I would assume is the PDF at this link (https://msdn.microsoft.com/en-us/library/mt807488.aspx) but I cannot seem to find a definitive list of what all 'formats' can go into the format parameter either in this document or on the web. Does anyone happen to know if that list exists somewhere that I can reference?

Since I can't seem to do quarters with Date.ToText I've instead opted to use the less elegant solution below so I'm not really stuck on that part, I just find it really strange that I can't find that documentation reference on the page.

each Number.ToText(Date.QuarterOfYear(_),"00")

 

[citizenbh]

Try this:

each Number.ToText(Date.QuarterOfYear([Date]),"00"))

Sory, I see you've this used

 

[citizenbh]

Function "Date.QuarterOfYear()" returns the number,
and
function "Date.ToText" is doing just that converts a date in the text.

 

[LockeGarmin]

Thanks for the reply! I was looking to know about the specification but I can live without knowing it. Thanks!

 

[MarcelBeug]

d --> day (1 or 2 digits)
dd --> day (2 digits)
ddd --> day (2 characters, culture dependent)
dddd --> (>=4x) day (full day name, culture dependent)
f or F (max 7x) --> fractions of seconds (max. 7 decimals)
g (>=1x) --> “A.D.” (in English, culture dependent, e.g. in Dutch “n.Chr.”)
h --> hour (1 or 2 digits according to 12-hours clock (e.g. 16 --> 4). H for 24-hours clock.
hh (>=2x) --> hour (2 digits according to 12-hours clock).
m minute --> (1 or 2 digits)
mm (>=2x) --> minute (2 digits)
s --> second (1 of 2 digits)
ss (>=2x) --> second (2 digits)
t --> A or P (in English, culture dependent)
tt (>=2x) --> AM or PM (in English, culture dependent)
y --> year (1 or digits)
yy --> year (2 digits)
yyy --> year (3 or 4 digits)
yyyy --> year (4 digits)
yyyyy (>= 5x) --> year (4 digits with leading zeroes)
z --> UTC offset hours with +/- (1 or 2 digits)
zz --> UTC offset hours with +/- (2 digits)
zzz (>= 3x) --> UTC offset with +/- in hh:mm. Alternative: K
H --> hour (1 or 2 digits according to 24-hours clock. h for 12-hours clock.
HH (>=2x) --> hour (1 or 2 digits according to 24-hours clock
K (>=1x) --> UTC offset with +/- in hh:mm (repeated for each K). Alternative: zzz
M --> month (1 or 2 digits)
MM --> month (2 digits)
MMM --> month (3 characters, culture dependent)
MMMM (>=4x) --> month (full month name, culture dependent)

 

[LockeGarmin]

Fantastic! Did you get that list from somewhere or was that just trial and error?

 

[MarcelBeug]

Both: I got it from my own documentation from trial and error some time ago.