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
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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,185)