Create Index Column Based on Month Year Column

Hi  Chinh_Dac 

Then feel free to use the following Query for Power Query:

let
  Source = List.Dates(#date(2021, 1, 1), 2000, #duration(1, 0, 0, 0)),
  ConvertedIntoTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  RenamedColumnDate = Table.RenameColumns(ConvertedIntoTable, {{"Column1", "Date"}}),
  AddColumnMonth = Table.TransformColumnTypes(Table.AddColumn(RenamedColumnDate, "Month", each Date.Month([Date])), {{"Month", Int64.Type}}),
  AddColumnYear = Table.TransformColumnTypes(Table.AddColumn(AddColumnMonth, "Year", each Date.Year([Date])), {{"Year", Int64.Type}}),
  AddColumWeekOfYear = Table.TransformColumnTypes(Table.AddColumn(AddColumnYear, "WeekOfYear", each Date.WeekOfYear([Date])), {{"WeekOfYear", Int64.Type}}),
  AddColumnMonthNameShort = Table.TransformColumnTypes(Table.AddColumn(AddColumWeekOfYear, "MonthNameShort", each Date.ToText([Date], "MMM")), {{"MonthNameShort", type text}}),
  AddColumnWeekDayName = Table.TransformColumnTypes(Table.AddColumn(AddColumnMonthNameShort, "WeekDayName", each Text.Start(Date.DayOfWeekName([Date]), 2)), {{"WeekDayName", type text}}),
  AddColumnWeekDayNum = Table.AddColumn(AddColumnWeekDayName, "WeekDayNum", each Date.DayOfWeek([Date])+1),
  ChangeDataType = Table.TransformColumnTypes(AddColumnWeekDayNum, {{"Date", type date}, {"WeekDayNum", Int64.Type}}),
  AddCoulmnPeriodYearMonth = Table.AddColumn(ChangeDataType, "Year-Month", each Number.ToText([Year]) & "-" & [MonthNameShort]),
  AddColumnPeriodYearMonthSort = Table.AddColumn(AddCoulmnPeriodYearMonth, "Year-Month (sort)", each [Year] * 100 + [Month]),
  AddColumnDateOfRefresh = Table.AddColumn(AddColumnPeriodYearMonthSort, "DateOfRefresh", each DateTime.LocalNow())
in
    AddColumnDateOfRefresh

It will provide you with a proper date table. If the granualrity is not right for you then you can delete columns and remove duplicate OR use the group by feauter.

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn