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
Ying_P
Helper I
Helper I

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

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!

 

Capture.JPG

 

 

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@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




Lima - Peru

View solution in original post

8 REPLIES 8

Just add an index column

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!

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

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. 

1.JPG2.JPG

 

Do you know how to solve this problem?

 

Great thanks!

 

Vvelarde
Community Champion
Community Champion

@Ying_P

 

What other columns do you have in Single Select.

 




Lima - Peru

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.

 

Capture.JPG

 

Thank you!

Vvelarde
Community Champion
Community Champion

@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




Lima - Peru

It works! Great thanks to you!!Smiley LOL

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.