Create a column with only dates of the current month

[PBIBeginner2022]

Hi everyone,

I have this column in my date table with date between 1899 to 2201 :

I need to create an other column in this table with dates of all day in current month. We are in july, I want only the date in format dd/mmmm/yyyy of the current month. And when we will in August I want only the same date for the august month and same for next month.

Thanks in advance.

[Vijay_A_Verma]

I am not sure how you want to insert the dates as a column, below is the formula to create a list of dates for current month which you can expand

= [d=Date.From(DateTime.FixedLocalNow()), s=Date.StartOfMonth(d), e=Date.EndOfMonth(d), Dates=List.Dates(s,Duration.Days(e-s)+1,#duration(1,0,0,0))][Dates]

[PBIBeginner2022]

@Vijay_A_Verma 

Thanks for your answer. It's that I want !!!! But when I insert your code, I see only the new column, I will keep my older column and I have in addtion my new column in the same table

[Vijay_A_Verma]

You can put that formula in a custom colum and then expand the column. If you needed different output, then if you can show me some sample output I can create the code for you. 

[PBIBeginner2022]

@Vijay_A_Verma ,

I atteched the code in advanced editor and my screen on this table in Power Query.

[PBIBeginner2022]

let
Source = Requête1(#date(1899, 1, 1), #date(2201, 12, 12), null),
#"Colonne Anee-Mois renommée" = Table.RenameColumns(Source,{{"Annee-Mois", "Mois-Année"}}),
#"Type modifié" = Table.TransformColumnTypes(#"Colonne Anee-Mois renommée",{{"DateRef", type date}})
in
#"Type modifié"

[Vijay_A_Verma]

So against, 1-1-1899, you need 7/1/2022.....1-31-1899, you need 7/31/2022 and from 1-Feb-1899, it will once again start with 7/1/2022 and so on...

OR

Against 1-1-1899, you need all dates for July and for 1-2-1899, all dates of July and so on... 

[PBIBeginner2022]

@Vijay_A_Verma 

In my table, I have all day between 01/01/1899 to 01/01/2201 as you can see below :

I need all days of the current month. At the moment  we are in July so I want an additional column with all days of July (the first of july to July the 31). In one month, we will in August and I want replace the value of this new coloumn by alls days of August and so on for all months of the year

[Vijay_A_Verma]

01/01/1899 to 01/01/2201 - It means that there would be 110304 cells in a column. Hence, when I insert a new column, it would contain 110304 cells. Now July would contain only 31 days.

If I populate 31 cells, then what should I populate in remaining 110304-31 cells.

Should I keep repeating July dates again and again or after populating 31 cells, I should keep remaining as blanks?

So question is about populating other than 31 cells...

[PBIBeginner2022]

@Vijay_A_Verma 

Yes, I would only 31 values and 110304 - 31 cells with no value. I need this to a chart in Powe BI DeskTop

[Vijay_A_Verma]

Use this

let
    Source = Requête1(#date(1899, 1, 1), #date(2201, 12, 12), null),
    #"Colonne Anee-Mois renommée" = Table.RenameColumns(Source,{{"Annee-Mois", "Mois-Année"}}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonne Anee-Mois renommée",{{"DateRef", type date}}),
    Result = Table.FromColumns(Table.ToColumns(#"Type modifié")&{[d=Date.From(DateTime.FixedLocalNow()), s=Date.StartOfMonth(d), e=Date.EndOfMonth(d), Dates=List.Dates(s,Duration.Days(e-s)+1,#duration(1,0,0,0))][Dates]},Table.ColumnNames(#"Type modifié")&{"Dates"})
in
Result

[PBIBeginner2022]

@Vijay_A_Verma 

I think we misunderstood each other. I don't want to replace the values but to have null on all my rows except on the corresponding rows. I want the date that in front of 01/07/2022 in the column DateRef, that there is 01/07/2022 in the column Dates for the row that corresponds to the same date. And that only for the current month. Do you understand? In the date column I only want the date in date format of the days in the month in which we are currently and the other dates in null.

[Vijay_A_Verma]

Then in a custom column named Dates, put following formula

= if Date.ToText([DateRef],"YYYYMM")=Date.ToText(Date.From(DateTime.FixedLocalNow()),"YYYYMM") then [DateRef] else null

Complete code would be

let
    Source = Requête1(#date(1899, 1, 1), #date(2201, 12, 12), null),
    #"Colonne Anee-Mois renommée" = Table.RenameColumns(Source,{{"Annee-Mois", "Mois-Année"}}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonne Anee-Mois renommée",{{"DateRef", type date}}),
#"Added Custom" = Table.AddColumn(#"Type modifié", "Dates", each if Date.ToText([DateRef],"YYYYMM")=Date.ToText(Date.From(DateTime.FixedLocalNow()),"YYYYMM") then [DateRef] else null, type date)
in
    #"Added Custom"

[PBIBeginner2022]

@Vijay_A_Verma Sorry but It's not what I search to do.

I want duplicate my DateRef column and delete all lines except date in the current month. Moreover I want a system which display each current month the date of this month.

In july 2022, I want the date of day of this month.

In august 2022, I want the date of day of the month of August 2022 and delete the date of july 2022.

And so on for the other month

[Vijay_A_Verma]

If you can post the mock-up of your result, that would be easier for us to give the solution. You can create the result in Excel and post a screenshot here.

[PBIBeginner2022]

@Vijay_A_Verma , I found the "errors". You have filtered on each July of each year. I only want day of current month and current year. Is it good for you ?

[Vijay_A_Verma · Accepted solution]

You can use either of the formulas, second was provided by @BA_Pete 

= if Date.ToText([DateRef],"yyyyMM")=Date.ToText(Date.From(DateTime.FixedLocalNow()),"yyyyMM") then [DateRef] else null

= if Date.IsInCurrentMonth([DateRef]) then [DateRef] else null

[PBIBeginner2022]

@Vijay_A_Verma, @BA_Pete  Thank you very much for your help

[BA_Pete]

Hi @PBIBeginner2022 ,

@Vijay_A_Verma has provided a working solution for you, but I think the following might be easier:

if Date.IsInCurrentMonth([dateRef]) then [dateRef] else null

Pete