question

stevekey99 avatar image
0 Votes"
stevekey99 asked emilyhua-msft commented

RANKX does not seem to be working for me.

I have this data:

84636-image.png



I would like the data in the Index column to not skip numbers but go in sequence -11112222222222233

I found definitions for the Supporting and Index columns in another location (sorry, I do not have the link). But here is what the responder advised:

The Supporting Column is created using:

=CALCULATE (
MIN ( Table[Index] ),
ALLEXCEPT ( Table, Table[id], Table[yr]))

Index column is created by:

=RANKX (ALL(Table),Table[Supporting Column],,ASC, Dense )

I cannot get the Index Column produce the mentioned result.

Please help.

power-query-not-supportedoffice-excel-itpro
image.png (23.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered emilyhua-msft commented

@stevekey99
I suggest you use the “Group By" feature in Power Query.

In Power Query, you may go to Home tab > Transform group > Group By, then choose "id" and "yr" for grouping basis, I set "Group Data" as a new column name and the "All Rows" for operation.
The deatiled settings and the result you may refer to the following image.
84852-capture5.png

Go to Add Column tab > General group > Add an index column from 1, then you would get the result as the image below.
84786-capture6.png

Please click on the expand toggle button located on the right of the "Group Data" column heading for expanding.
I tick the box of "qtr" and ""Amount" to expand data. The index number would be left after doing expanding.
84853-capture7.png

At last, you could modify the column name for "qtr" and "Amount" as following screenshot.
84729-image.png


If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


capture5.png (25.8 KiB)
capture6.png (23.8 KiB)
capture7.png (29.2 KiB)
image.png (39.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@stevekey99
Was my workaround helpful?
If you need further assistance, please feel free to post back.

0 Votes 0 ·