Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
guyscottsouth
Frequent Visitor

index of repeating numbers

Hi, 

 

I think this is proably quite straightforward, but I can't seem to get my head around it.

 

I want to create an index column that indicates the occurence of that value in the column sorted by date

 

DateUnit IDIndex
01/01/20171011
02/01/20171012
03/01/20171211
04/01/20171111
05/01/20171112
06/01/20171212
07/01/20171013
08/01/20171213

 

any help would be greatly appreciated

 

regards

 

Guy

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

You can group on Unit ID with operation "All Rows".

Next adjust the generated to have an Index added to the nested tables while grouping.

Then expand and sort on date.

 

let
    Source = Table1,
    Grouped = Table.Group(Source, {"Unit ID"}, {{"AllData", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    Expanded = Table.ExpandTableColumn(Grouped, "AllData", {"Date", "Index"}, {"Date", "Index"}),
    Reordered = Table.ReorderColumns(Expanded,{"Date", "Unit ID", "Index"}),
    Sorted = Table.Sort(Reordered,{{"Date", Order.Ascending}})
in
    Sorted

 

 

If the data is not sorted on date and you want to get the original sort back, then you can add an Index at the first step (before grouping - call this index e.g. "OriginalSort" to prevent duplicate name "Index") and at the end sort on the OriginalSort and remove this.

Specializing in Power Query Formula Language (M)

View solution in original post

6 REPLIES 6
iamprajot
Responsive Resident
Responsive Resident

Measure/Column=CALCULATE(RANKX(ALL('Table'[Unit ID]),CALCULATE(SUM('Table'[Unit ID]))),ALLEXCEPT(Table,Table[Date],Table[Unit ID]))

Anonymous
Not applicable

Can you please explain how this works?

@iamprajot 

CheenuSing
Community Champion
Community Champion

Hi @Guy

 

Follow the link

https://community.powerbi.com/t5/Desktop/Custom-column-Index-or-Ranking-by-other-column/td-p/33864


where @Imekf provided a solution to similar problem.

 

The solution is adding the following lines in the queryeditor.

let
    Source = Table1,
    Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded Partition"

 

Using this and the data provided I could get what you wanted.

 

Capture12.GIF

 

If the linked in article solves your issue kindly accept this as a solution and also give KUDOS to me and @Imekf

 

Cheers

 

CheenuSing

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@CheenuSing that looks like you confirm my solution, so thanks!

Specializing in Power Query Formula Language (M)
MarcelBeug
Community Champion
Community Champion

You can group on Unit ID with operation "All Rows".

Next adjust the generated to have an Index added to the nested tables while grouping.

Then expand and sort on date.

 

let
    Source = Table1,
    Grouped = Table.Group(Source, {"Unit ID"}, {{"AllData", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    Expanded = Table.ExpandTableColumn(Grouped, "AllData", {"Date", "Index"}, {"Date", "Index"}),
    Reordered = Table.ReorderColumns(Expanded,{"Date", "Unit ID", "Index"}),
    Sorted = Table.Sort(Reordered,{{"Date", Order.Ascending}})
in
    Sorted

 

 

If the data is not sorted on date and you want to get the original sort back, then you can add an Index at the first step (before grouping - call this index e.g. "OriginalSort" to prevent duplicate name "Index") and at the end sort on the OriginalSort and remove this.

Specializing in Power Query Formula Language (M)

Thanks for the response, it did fully answer the question I asked so it;s my fault for not being more specific. I had hoped to do this through a calculated column in DAX rather than through the query editor as the table itself is a calculated table. Is this possible?

 

thanks

 

Guy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.