Index and year_month

[SRK_23]

Hi-- I would like to create (in the desktop version) a table call "Date" with unique YearMonth field and an index. Like that:

Index:     Year_Date:

1               2013/01

2               2013/02

3               2013/04

4               2013/05

Starting in 2013/01 and finishing 2025/12

How do I do that ?

Thanks

[PattemManohar]

@SRK_23 Please create a "New Table" as below

Test141DateGen = 
VAR _Index = SELECTCOLUMNS(GENERATESERIES(1,12),"Index",[Value])
VAR _Date = SELECTCOLUMNS(GENERATESERIES(2013,2025),"Year",[Value])
RETURN CROSSJOIN(_Index,_Date)

Then, two new fields which will be final output as expected

YYYY/MM = Test141DateGen[Year] & "/" & IF(LEN(Test141DateGen[Index])=1,"0"&Test141DateGen[Index],Test141DateGen[Index])

IndexFinal = RANK.EQ(Test141DateGen[YYYY/MM],Test141DateGen[YYYY/MM],ASC)

[Zubair_Muhammad · Accepted solution]

@SRK_23

Using Query Editor/ Power Query

Create a Blank Query>>Go to Advanced Editor and copy paste this

let
    Source = {2013..2025},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each {1..12}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Year_Date", each Text.From([Column1])&"-"&Text.End("0"&Text.From([Custom]),2)),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Column1", "Year"}, {"Custom", "Months"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1)
in
    #"Added Index"