Date to YYYYMM format with DirectQuery

[samurai_jack]

Hi, I have a Date column (dd-mm-yyyy) that I would like to transform into yyyymm. My table is connected via DirectQuery. I used the following m-query for the transformation:

Number.ToText(Date.Year([Date]))&Number.ToText(Date.Month([Date]))

However I am missing the '0' in front of the single months. Any ideas on how this can be resolved?

Thank you!

[AlexisOlson · Accepted solution]

You can use Date.ToText to create a custom column like this:

Date.ToText([Date], [Format="yyyyMM"]))

This doesn't work with DirectQuery though.

For DirectQuery, try this:

100 * Date.Year([Date]) + Date.Month([Date])

You can either keep this as an integer or convert it to text.

[samurai_jack]

Thank you very much, this works!