Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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"
@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)
Proud to be a PBI Community Champion
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"
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |