Bringing back dates of occurrences within set weeks of a month

[Bexx]

Good morning, I'm new to power BI and need to be able to bring back attendances within specified weeks in a month - i.e. week 1 

So if the week 1 commencement date is 20/06/2022 and the attendance is 24/06/2022 it will appear in week 1 but if it's 28/06/2022 it will appear in the week 2 column, does anyone know how to do this?

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test. The steps useful for you start from #"Added Custom"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7NLMlQ0lEy0zcyACIjI6VYnWgl54zEopzUYoi4GULcJ7M4ESJojqw4taioEiJsgaQ2NR8oZq5vaAoViwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Student = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student", type text}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each "Week "&Text.From(Number.RoundUp((Duration.Days([Date]-#date(2022,6,19)))/7,0))),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Custom]), "Custom", "Date")
in
    #"Pivoted Column"