Add a serial number to a new table created in Power BI

[Ying_P]

Hi all,

I wanted to sort data by customized date, so I created a table as below. Then I couldn't figure out how to add a column showing the row number of each. Does someone know about that?

I appreciate your help!

[dkay84_PowerBI]

Just add an index column

[Ying_P]

Thank you so much for your reply.

Could you show me where I can add it? I tried to add an index column under Modeling, but it didn't work; I also tried to edit queries, but the new table I created did not show in the list of tables. 

Thanks!

[dkay84_PowerBI]

You can find Index Column as an option in the ribbon in the query editor.

[Ying_P]

Thank you!

I found the index column in query editor, but the problem is, the table I created did not show up, so I was not able to edit queries. 

Do you know how to solve this problem?

Great thanks!

[Vvelarde]

@Ying_P

What other columns do you have in Single Select.

[Ying_P]

There are a bunch of columns in this table, some are texts, some are numbers. Basically, I created a new table under the Data view - Modeling, and used one of the variables (Month/Year) in the single-select table.

What I want to do is similar to this question:

http://community.powerbi.com/t5/Desktop/Custom-Sorting-in-PowerBI/td-p/126534

but I just could not figure out how to get the numbers in the ID column in the newly created table shown below.

Thank you!

[Vvelarde · Accepted solution]

@Ying_P

Hi, let me propose a solution to this:

Step1:

Create a Table with 2 Columns instead of your Month-year Table

MonthYearTable = DISTINCT(SELECTCOLUMNS(Table1,"Month-year",Table1[Month-Year],"Month-year-alt",Table1[Month-Year]))

Step 2:

Change the Data Type to Month-year-alt to Date

Step 3:

Create a New Calculated Column:

MonthSort = RANKX(MonthYearTable,MonthYearTable[Month-year-alt],,ASC)

Step 4: Select your first Column: Month-year and go to Modeling-Sort by Column and choose MonthSort

Ready

[Ying_P]

It works! Great thanks to you!!