Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SRK_23
Helper I
Helper I

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

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

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
PattemManohar
Community Champion
Community Champion

@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 PBI Community Champion




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

 

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.