cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SRK_23 Regular Visitor
Regular Visitor

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/02

3               2013/04

4               2013/05

 

 

Starting in 2013/01 and finishing 2025/12

 

How do I do that ?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Index and year_month

@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"

 

 

2 REPLIES 2
Super User
Super User

Re: Index and year_month

@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)

image.png

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Highlighted
Super User
Super User

Re: Index and year_month

@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"

 

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 117 members 1,665 guests
Please welcome our newest community members: