Forum Discussion

SRK_23's avatar
SRK_23
Icon for Helper I rankHelper I
7 years ago
Solved

Index and year_month

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/...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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"

     

     

PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
7 years ago

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's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
7 years ago

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"