Adding new index

[umasankar30]

i want to add index in power query with folder data, after creating index, i want to add new index for newly added folder in same folder path without affecting the old index.

let
Source = Folder.Files("C:\\Umasankar\\Tenders\\B&R"),

#"Inserted Tender Name" = Table.AddColumn(Source, "Tender Name", each Text.BetweenDelimiters([Folder Path], "\", "\", 6, 0), type text),
#"Inserted Company" = Table.AddColumn(#"Inserted Tender Name", "Company", each Text.BetweenDelimiters([Folder Path], "\", "\", 5, 0), type text),

// Remove Unnecessary Columns
#"Removed Columns" = Table.RemoveColumns(#"Inserted Company",{"Content", "Date accessed", "Attributes", "Folder Path"}),

// Reorder Columns
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Name", "Extension", "Date modified", "Company", "Tender Name", "Date created"}),

// Change Data Types
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date created", type date}, {"Date modified", type date}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Changed Type",{"Company", "Tender Name", "Date created", "Date modified", "Name", "Extension"}),

// Remove Unnecessary Columns
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"Name", "Extension"}),

// Remove Duplicates
#"Removed Duplicates" = Table.Distinct(#"Removed Columns1", {"Tender Name"}),

// Add Index Column
#"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type),

// Reorder Columns
#"Reordered Columns2" = Table.ReorderColumns(#"Added Index",{"Company", "Index", "Tender Name", "Date created", "Date modified"}),

// Combine Company and Index
#"Added Custom" = Table.AddColumn(#"Reordered Columns2", "Tender No", each Text.Combine({[Company], Text.From([Index])}, "")),
#"Reordered Columns3" = Table.ReorderColumns(#"Added Custom",{"Tender No", "Company", "Index", "Tender Name", "Date created", "Date modified"}),

// Remove Index Column
#"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns3",{"Index"})
in
#"Removed Columns2"

[v-ssriganesh]

Hi @umasankar30,

I hope you're doing well. I wanted to follow up once more regarding your query. We haven't received the requested details yet, and we need this information to assist you effectively.

Thank you.

[rohit1991 · Accepted solution]

updated code:

let
    Source = Folder.Files("C:\\Umasankar\\Tenders\\B&R"),

    // Extract folder metadata
    #"Inserted Tender Name" = Table.AddColumn(Source, "Tender Name", each Text.BetweenDelimiters([Folder Path], "\", "\", 6, 0), type text),
    #"Inserted Company" = Table.AddColumn(#"Inserted Tender Name", "Company", each Text.BetweenDelimiters([Folder Path], "\", "\", 5, 0), type text),

    // Remove unnecessary columns and clean up
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Company",{"Content", "Date accessed", "Attributes", "Folder Path"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Tender Name"}),

    // Load old indexed data (replace with actual query or table reference)
    OldData = YourOldIndexedTable,

    // Find new rows (anti-join)
    NewRows = Table.NestedJoin(#"Removed Duplicates", "Tender Name", OldData, "Tender Name", "OldData", JoinKind.LeftAnti),

    // Add new index to new rows
    #"Added New Index" = Table.AddIndexColumn(NewRows, "Index", Table.RowCount(OldData) + 1, 1, Int64.Type),

    // Combine old and new data
    CombinedData = Table.Combine({OldData, #"Added New Index"}),

    // Final cleanup (optional)
    #"Sorted Data" = Table.Sort(CombinedData, {{"Index", Order.Ascending}})
in
    #"Sorted Data"

Key Notes:

• Replace YourOldIndexedTable with your query or table reference that holds the old indexed data.
• The JoinKind.LeftAnti ensures only new rows are indexed.
• The Index for new rows starts from the last index of the old data (Table.RowCount(OldData) + 1).
  
  
  

for further reference please visit https://support.microsoft.com/en-us/office/add-an-index-column-power-query-dc582eaf-e757-4b39-98e6-b...

[v-ssriganesh]

Hi @umasankar30 ,
Could you please confirm if this issue has been resolved? If so, kindly accept it as a solution.

Thank you.

[v-ssriganesh]

Hi @umasankar30,

Thank you for posting your query in the Microsoft Fabric community.

Could you please provide a few details? If possible, could you share your .pbix file with me? Additionally, I would appreciate it if you could describe the expected behavior you're looking for.
Thankyou.

[v-ssriganesh]

Hi @umasankar30,
I am following up to see if you had a chance to review my previous response and provide the requested information. This will enable us to assist you further.

Thank you.

[sanalytics]

@umasankar30 
Do not understand your questions properly. Can you please post your input along with your desired output. So that we can help in this regard.

Regards

sanalytics