Forum Discussion

JacoMathew's avatar
JacoMathew
Frequent Visitor
4 years ago

Create Index on a table in Data pane and not in Power Query

Hi,

I have created a simple list of String using the Create Table in Data pane. I wanted to create a column that shows the row number. I came accros RankX but that sorts using the string and assign a Rank. What I want is the row number should be assigned irrespective of the order of string, similar to the index function in Power Query. I tried to use the Generateseries() function but it shows error that GenerateSeries returns a table. Here is what I have tried :

Created a table that has 30 items. The order of the list has to be based on the given list and not alphabetical.

TopN =
{
"Indian Bank",
"Canara Bank",
"UCO Bank",
"Indian Overseas Bank",
....
}

 

I tried to add a column using GenerateSeries function

Order = GENERATESERIES(1,COUNTROWS(TopN),1)
 

But this gives me an error as GenerateSeries returns a table.

 

Is it possible to add generateseries value when I create the table in the below method ?

TopN =

VAR _List =
{
"Canara Bank",
"Indian Bank",
"Indian Overseas Bank",
"UCO Bank",
....
}


Var _Order =
GENERATESERIES(1, COUNTROWS(_List), 1)
 
RETURN

( A table should be returned with Column Name "List" = _List and "Order"= _Order)

I thought a running total might work but was unsuccessful. I created a column with just a value 1 after creating the list and thought I could do sum of all the 1 values upto current row. Couldn't get that to work as Running total uses a Rank which is created by sorting the List string. 

 

Any suggestion on how I could create a table with a list and an order column based on the number of rows in the list ?

Thanks,

 

Jaco

 
Need Help

4 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    You could create it using a veeeery long SWITCH expression...

    However, if the list is static, I would probably create it in Excel including the index column and copy/paste it into a table using "Enter Data" in the ribbon.

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    JacoMathew 

    I doubt you can have a number sequence without associating it to an existing column or columns. What stops you from creating a table in Power Query and adding an Index Column.?

    • JacoMathew's avatar
      JacoMathew
      Frequent Visitor
      4 years ago

      I know it can be done with Power Query. I wanted to know if it can be done from the Data pane. Thought I was missing something simple. Has happened to me in the past. I guess for now, I will move the table creation to Power Query.

       

      I used switch expression for another table but that had only 5 items.