One date to Calendar Date

[syelify]

Hello,

I have one specific date column, and I would like to have a date (in result - image showing below)

Is there any possible queries to do this?

Thanks,

[BA_Pete · Accepted solution]

Hi @syelify ,

So it looks like you want to crossjoin every day of a given month with any date that sits within that month.

If so, you can add a custom column like this:

List.Transform(
  {
    Number.From(Date.StartOfMonth([Date]))..Number.From(Date.EndOfMonth([Date]))
  },
  each Date.From(_)
)

Expand the resulting list to new rows and this should give you what you want.

Pete

[syelify]

Thanks it works 🙂

[BA_Pete · Accepted solution]

@syelify ,

Glad it's worked for you.

If you're happy with this answer, can you hit 'Accept as Solution' on the relevant post to help others find the answer quicker please?

Pete

[BA_Pete · Accepted solution]

Hi @syelify ,

So it looks like you want to crossjoin every day of a given month with any date that sits within that month.

If so, you can add a custom column like this:

List.Transform(
  {
    Number.From(Date.StartOfMonth([Date]))..Number.From(Date.EndOfMonth([Date]))
  },
  each Date.From(_)
)

Expand the resulting list to new rows and this should give you what you want.

Pete