Count of weeks in a 4,5 week Month

[Anonymous]

Dear Experts,

Please see the below table list.I have managaed to get the Month name in the adjacent column.Hint- Week 40-43 belongs to Oct,44-47 for Nov and rest weeks with months with same logic however I'm not sure how to calculate the simple logic of count of weeks in another custom column i.e Oct should show 4 for each instanct of corresponding week i.e week 40 =4,week 41=4,week 42=4,week 43=4 ,likewise Dec should show 5 as it has 5 weeks

Thanks in advance.

Week- Month

Week 40 FY22
Week 41 FY22
Week 42 FY22
Week 43 FY22
Oct 22
Week 44 FY22
Week 45 FY22
Week 46 FY22
Week 47 FY22
Nov 22
Week 48 FY22
Week 49 FY22
Week 50 FY22
Week 51 FY22
Week 52 FY22
Dec 22

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week- Month", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Start([#"Week- Month"],4)="Week" then null else DateTime.FromText([#"Week- Month"]), type date),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each Text.StartsWith([#"Week- Month"], "Week")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Custom"}, {{"GroupTables", each _, type table [#"Week- Month"=nullable text, Custom=date]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "CountRows", each Table.RowCount([GroupTables])),
    #"Expanded GroupTables" = Table.ExpandTableColumn(#"Added Custom1", "GroupTables", {"Week- Month"}, {"Week- Month"})
in
    #"Expanded GroupTables"

Hope this helps.

[Anonymous]

Thanks a lot however month is in text format not date i.e Oct,Nov in my raw data - do you think it will still work?

[Ashish_Mathur]

Try it.

[Anonymous]

It worked thanks a lot..

[Ashish_Mathur]

You are welcome.

[Anonymous]

@Ashish_Mathur @amitchandak @lbendlin @ray_ux @tamerj1