Index column in power query based on date sorting

[lenafherold]

Hello,

I have seen similar posts to this but wanted to throw a question out there--I have created a running totals column in the power query, which I would like to generate based on the date in ascending order. My table is not automatically sorted by date, so I added a step in the power query to "sort" prior to adding an index column. It seems to work initially, but when I apply the steps, the index follows the natural order of the observations, rather than sorting by date. 

Is there a quick workaround to this?

Essentially, to ensure that an index column follows a sort filter that I have applied?

I have attached a screenshot of my columns for reference--the running total is currently not being calculated in chronological order. photo

[BA_Pete]

Hi @lenafherold ,

Did you get a chance to see whether either of the answers to your question were helpful at all?

If an answer helped you solve your issue, please mark it as the solution.

If you're still having issues, please explain in more detail where the answers provided are missing the point and we can revisit to get this sorted for you.

Pete

[Anonymous]

Hi @lenafherold ,

I think you want an index column grouped by the date column.

Here's my solution.

1.Right-click on the date column and select Group By.

2.Select All Rows.

3.Add an index column.

4.Expand the column.

You can check more details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[atifmir]

Excellent, i was looking for this very solution. Thanks!

[BA_Pete]

@Anonymous

@v-jingzhang 

Hi both.

I've removed the solution applied to this post as it doesn't appear to actually provide a solution to OP's question. Happy to discuss in more detail if required.

Pete

[BA_Pete]

Hi @lenafherold ,

It looks from your screenshot that it's working fine.

Power Query sometimes jumbles up the rows on certain operations e.g. merges etc., but it has also jumbled the index accordingly, so your cumulative sums are still calculating in the right order.

In the instance that sorted dates all have equal values (as yours mostly do), I believe the sort then reverts to the left-most column order, then next one etc. As such, when you apply this to the model, and the AS engine swaps all your columns around, then you sort again on your date column, the sort criteria has changed and therefore so does your sort order. In this instance, you should sort on the index column in the data model to get back to exactly the same order the rows were in when you applied the index in PQ.

Not sure if there was another issue that I missed?

Pete