Expand to full list of dates with only Month and Year

[SuperJK5088]

Hi PBI Grurs! 

I have a table with month and year columns. How can I transform the table to have a new column with dates of every day?

Please see screenshot. The code is only giving me the first day of each month in "Date" column. For example, if the month is 1 and year is 2023, I need to date column to have 1/1/2023 - 1/31/2023 in row levels. 

Please help - TIA!

[adudani · Accepted solution]

hi @SuperJK5088 ,

after converting the "Date" Column to type date.

using the "add column" ribbon -> from "date & time" -> date-> end of month.

use list.dates to get a list of dates from a start dat and end date as shown in this reference below:

https://www.youtube.com/watch?v=s_ClydymBW4&t=1s 

if you have any issues, kindly provide a sample input and for further resolution

[Chewdata · Accepted solution]

Hey,

You can use the following code for a custom column and then expand the list.

= Table.AddColumn(#"Changed Type", "ListDates", each List.Dates(
#date([Year], [Month], 1),
Duration.Days(
Date.EndOfMonth(#date([Year], [Month], 1)) - #date([Year], [Month], 1)) + 1, #duration(1,0,0,0)))

[adudani · Accepted solution]

hi @SuperJK5088 ,

after converting the "Date" Column to type date.

using the "add column" ribbon -> from "date & time" -> date-> end of month.

use list.dates to get a list of dates from a start dat and end date as shown in this reference below:

https://www.youtube.com/watch?v=s_ClydymBW4&t=1s 

if you have any issues, kindly provide a sample input and for further resolution