Power Query to return every 7th of each month based on a date (7th ,14th ,21st ,28th)

[jjkh58]

Hi guys, 

I have a column called "Payment Date" (as per the screenshot below). Based on these payment dates, I need to make a new column where it captures the payment date which were made on 7th , 14th, 21st and 28th only. 

1. What sort of fomula or function do I need use for this occasion? - Only need to capture the ones with 7th,14,21,28 on "Payment Date" Column

2. If required, how can I add/ aggregate the payments (column right next to "Payment Date", not showin in the screenshot) for dates between 7th~28th only based on dates in "Payment Date" column

Cheers,

[VijayP]

@jjkh58 
Create a custom Column and in that Type this Formula ( Just example given and you need to replace your values in the formula)
Table.AddColumn(#"Changed Type", "Custom", each if Date.Day([DAte]) = 4 or Date.Day([DAte]) = 12 then [DAte] else null)
This will create a column having only dates of mentioned by you and you can filter the table by new column and use that data in the data model

[jjkh58]

Hi Vijay,

Thanks for your reply. 

Could you please tell me where exactly do I need to change in that fomula? 

Thanks, 

[Anonymous · Accepted solution]

Hi @jjkh58 ,

You can follow the suggestion from @VijayP  to add custom column as below screenshot in Power Query Editor. Also you can refer the following documentation to do it.

Add a custom column in Power BI Desktop

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc67CQAgEATRXowF3fNfi9h/GyYnOMnAy2bvEE70lDSSZZNj/lg/lCFBBhWoQg3qEE6EFeHF3su5", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Payment Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Payment Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Date.Day([Payment Date]) = 7 or Date.Day([Payment Date]) = 14 or Date.Day([Payment Date]) = 21 or Date.Day([Payment Date]) = 28 then [Payment Date] else null)
in
    #"Added Custom"

Best Regards