Dynamic Columns and Grouping: Getting X year from a list of years

[GlassShark1]

Struggling to find ways of maniplating dataframes dynamically. 

When pivoting a column, is there any way of keeping track of how many columns you have created? Or is there any way of tracking the 'position' of a date value within a list (e.g. 'the date value 31/03/2022 is the third value in the list sequentially')

My Fact table has a column for sales year (always 31st March 20XX), and another column for number of products sold. I pivot this to get these dates as columns and sales as values, so i can then replace any null values for the next part of my calculation, where i unpivot back again so each product has a row regardless if it has a sale in that year or not (I do it this way for a cummulative calculation). 

Later i want to be able compare these figures to a rolling target. Say our sales target for 2030 was 1,000, i would then divide the target by however many years to get a position we should ideally be in at a particular year (e.g. at year '3', it should be 3 x 10/1000). What i'm stuck on is how to get to "there are 6 dates in the column, X is the third date in the list sequentially' so i can then apply the target calculation.

Any ideas?

Thanks 🙂  

[v-robertq-msft · Accepted solution]

Hi, @GlassShark1 

According to your original thread description, I guess you want to track the 'position' of a date value within a list, I think you can achieve this by adding an index column in the Power query editor, like this:

Before adding the index column, you should sort ascending to make sure the date column is in positive order.

If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[GlassShark1]

Sorry - what i'm trying to do as a whole is quite complicated. 

For this bit though, i'm basically asking how in a column of dates (essentally years), i can 'rank' them - so '1' is the earliest, '2' the next, etc etc.

Annoyingly thinking about it, i can probably do this with RANKX?

Thanks 🙂

[amitchandak]

@GlassShark1 ,The information you have provided is not making the problem clear to me. Can you please explain with an example. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Appreciate your Kudos.