Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I need to add a Index-ID column for same set of column values.
How to generate automated IDs? example below. Thanks.
Solved! Go to Solution.
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
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
Proud to be a 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
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
Proud to be a Super User!
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])))
Proud to be a Super User!
Hi,
Thanks for your quick reply.
I got struck with only Earlier func, pls see the below screenshot.
Looks like you are creating a measure. You should create a Calculated Column
Proud to be a Super User!
Hi,
I got result but Id started with some randam numbers.
I need Index number from "1". Thanks so far your help.
Sorry to truble you alot.
My Original Data looks like below.
Are you creating Index Column from Power Query?
Is your segmentation also included dates?
Can you please share some sample data along with your table structure and desired output?
Proud to be a 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])
Hi,
Thanks for your quick reply.
I get error in Earlier Func.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
69 | |
55 | |
37 | |
35 |
User | Count |
---|---|
85 | |
66 | |
59 | |
46 | |
45 |