Fixed Index Column

[Lightsoutflyer3]

I need to be able to create an index column that does not change when new data is added.

I have a sharepoint folder set up with a daily file added and connected to Power BI. I need to be able to incorporate an index column to try and create a unique identifier with another ID field. The report can have multiple rows with the exact same data but should be treated separately. I am hoping adding an index column and merging will fix the issue. But after creating an index field and merging columns, the index number changes by row when new data is added.

I have tested different sorting options with no success.  I also tried something with table.buffer

[v-pgoloju]

Hi @Lightsoutflyer3,

Thanks for reaching out to the Microsoft Fabric Forum Community.

And also thaks to @PwerQueryKees for Prompt and helpful solution.

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful  this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[PwerQueryKees · Accepted solution]

Im am not sure I fully understand you problem.

You create a unique ID but you do not specify at what level the reords are unique and when they are considered duplicates.

You Unique-Id is a combination of ID and Index, but the index is already unique, so what is the benefit here?

So I guess, The ID is already unique, but you can get multiple rows for the same ID. 

How do you want the index to be created? My solution below gives you a unique ID for each unique combination of all field.

But does that solve you problem. Yes, if you are ok with a duplicate index (but unique ID) when any of the other columns differs. 

From

To

Using

let
    // your Daily table
    Source = Excel.CurrentWorkbook(){[Name="Daily"]}[Content], 
    // Save the column names for later
    #"Column Names" = Table.ColumnNames(Source),  
    // Group on all columns to get 1 row for all duplicated rows and add an index to the result
    #"Grouped Rows" = Table.Group(Source, #"Column Names", {{"Index", each  Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table }}),  
    // Remove the origibal columns (because they are no part of the table in the "Index" column
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows", #"Column Names"),  // remove the original columns
    // Return the grouped rows and remove the Index column
    #"Expanded Index" = Table.ExpandTableColumn(#"Removed Columns", "Index", List.Combine({#"Column Names", {"Index"}})), 
    // Create the unique ID
    #"Added Custom" = Table.AddColumn(#"Expanded Index", "Unique Id", each Text.Combine({Text.From([ID]),Text.From([Index])},"-"))
in
    #"Added Custom"

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[Lightsoutflyer3]

Thank you!  I incorporated what was included above and it solved the problem I was having.