Query Editor - Transformation - Replace values: use of wildcard

[Anonymous]

Hi There, 

I've got a table with 2 columns year and month. I need to add a column with a specific date format. I can do so by adding a column to the table itself in using Date(year([year]), month([month],1) for example. My question is which formula to use if I want to add this date field in power query editor. "Date" appears to not be recognised. 

thanks!

[Buzz1126]

I think the easiest way to do this is by creating a Date Table.

Go to Modeling at the top -> New table

At the top enter:

DATE = CALENDAR(DATE(2011,1,1), DATE(2019,12,31))
'You can change the 2011/2019 dates to whatever you want.  

This will create a date data table, link it to your data throgh relationships and you'll be good to go.

[Anonymous]

thanks for your response. my problem is however that my table only contains the columns 'year' and 'month' and I need to add a custom column in date format.  I have a calendar table to which I want to link my table, using this additional column I'm struggling to create. 

[v-danhe-msft · Accepted solution]

Hi @Anonymous ,

What is your desired result? If you just want to create a custome date with your [Year] and [Month] columns, you could refer to below formula in query editor:

#date([Year],[Month],1)

Result:

You could also refer to date function in M:

https://docs.microsoft.com/en-us/powerquery-m/date-functions

Regards,

Daniel He

[Anonymous]

thanks for that, I had tried this, but overlooked the case sensitivy of the date function. am all set now.