Create Index Column Based on Month Year Column

Chinh_Dac 

As I described above you can delete columns you do not need and adjust the table like this:

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()),
    #"Removed Other Columns" = Table.SelectColumns(AddColumnDateOfRefresh,{"Year-Month (sort)"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

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