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
Anonymous
Not applicable

How to add sequence number for repeated group of words?

Hi,

I need to add a Index-ID column for same set of column values. 

How to generate automated IDs? example below. Thanks.

MuraliPrasathS_0-1611213332527.png

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @Anonymous 

A solution totally in Power Query, download this PBIX file for the code :

1. Add an Index Column from 1

2. Add a Custom Column with this code

= Table.AddColumn(#"Added Index", "ID", each Number.RoundUp([Index]/3,0))

3.Remove the Index Column

 

index-group.png

 

You can easily modify this code.  For example if you have repeated groups of 5 words, just change the 3 in Step 2 above to 5 i.e.

= Table.AddColumn(#"Added Index", "ID", each Number.RoundUp([Index]/5,0))

 

Here's the full exampe code or download this PBIX file

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs/PT1GK1YlWcixLLUpMTwWznRIhYrSQjAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "ID", each Number.RoundUp([Index]/3,0)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"ID", Int64.Type}})
in
    #"Changed Type"

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

8 REPLIES 8
PhilipTreacy
Super User
Super User

Hi @Anonymous 

A solution totally in Power Query, download this PBIX file for the code :

1. Add an Index Column from 1

2. Add a Custom Column with this code

= Table.AddColumn(#"Added Index", "ID", each Number.RoundUp([Index]/3,0))

3.Remove the Index Column

 

index-group.png

 

You can easily modify this code.  For example if you have repeated groups of 5 words, just change the 3 in Step 2 above to 5 i.e.

= Table.AddColumn(#"Added Index", "ID", each Number.RoundUp([Index]/5,0))

 

Here's the full exampe code or download this PBIX file

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs/PT1GK1YlWcixLLUpMTwWznRIhYrSQjAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "ID", each Number.RoundUp([Index]/3,0)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"ID", Int64.Type}})
in
    #"Changed Type"

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


FarhanAhmed
Community Champion
Community Champion

You can try it by adding an Index column using Power Query 

 

and create a calculated column like this.

 

Group Status = CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[Status]),'Table'[Index]<=EARLIER('Table'[Index])))

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Anonymous
Not applicable

Hi,

Thanks for your quick reply.

I got struck with only Earlier func, pls see the below screenshot.

MuraliPrasathS_0-1611219654036.png

 

Looks like you are creating a measure. You should create a Calculated Column







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Anonymous
Not applicable

Hi, 

I got result but Id started with some randam numbers.

I need Index number from "1".  Thanks so far your help.

MuraliPrasathS_0-1611220678077.png

 

Sorry to truble you alot. 

My Original Data looks like below.

MuraliPrasathS_1-1611220888438.png

 

Are you creating Index Column from Power Query?

FarhanAhmed_0-1611221424466.png

 

Is your segmentation also included dates?

 

Can you please share some sample data along with your table structure and desired output?







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




amitchandak
Super User
Super User

@Anonymous , Add an index column in power query and then add this new column in dax

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

 

Countx(filter(Table, [status] =earlier([status]) && [index] <= earlier([index])),[index])

Anonymous
Not applicable

Hi,

Thanks for your quick reply.

I get error in Earlier Func.

MuraliPrasathS_1-1611220156477.png

 

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.