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

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

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

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

@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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

8 REPLIES 8
Highlighted
Microsoft dkay84_PowerBI
Microsoft

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

Just add an index column

Ying_P Regular Visitor
Regular Visitor

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

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!

Microsoft dkay84_PowerBI
Microsoft

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

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

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

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 Super Contributor
Super Contributor

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

@Ying_P

 

What other columns do you have in Single Select.

 




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

Proud to be a Datanaut!




Ying_P Regular Visitor
Regular Visitor

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

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 Super Contributor
Super Contributor

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

@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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Ying_P Regular Visitor
Regular Visitor

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

It works! Great thanks to you!!Smiley LOL

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)