Forum Discussion
Adding new index
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"
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-bb59ae44aa82
6 Replies
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
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.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.
Hi umasankar30 ,
Could you please confirm if this issue has been resolved? If so, kindly accept it as a solution.Thank you.
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-bb59ae44aa82
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.
