Forum Discussion
Create index column based on criteria (Power Query)
Dear All,
I would like to create a kind of index column based on conditions.
I have a product ID column and I would like to create an index column in Power Query, which restarts when the product ID changes & the indexing follows the cronological order of date column:
Thank in advacne for all tips.
Hello BalazsNy
Group By Product ID (in Power Query).
Operation for All Rows.
After grouping, add a Custom Column with the formula
=Table.AddIndexColumn(_, "Wished index outcome", 1, 1, Int64.Type)
Expand the resulting tables to flatten the data back out.
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
5 Replies
Go to the "Add Column" tab.
Click on "Index Column" and select "From 1".
Group by Product ID:Go to the "Home" tab and click on "Group By".
Group by the Product ID column.
Add a new column with the operation "All Rows".
Add a Custom Column for the Index:Click on the "Add Column" tab.
Click on "Custom Column".
Use the following formula to create a custom index that restarts for each product group:
Table.AddIndexColumn([All Rows], "Index", 1, 1, Int64.Type)
Expand the Grouped Table:Click on the expand icon next to the new column created in the grouping step.
Select all columns except the original index column.
Remove Unnecessary Columns:Remove the original index column if it is no longer needed.
Hello BalazsNy
Group By Product ID (in Power Query).
Operation for All Rows.
After grouping, add a Custom Column with the formula
=Table.AddIndexColumn(_, "Wished index outcome", 1, 1, Int64.Type)
Expand the resulting tables to flatten the data back out.
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
hi pankajnamekar25
Thanks for the tip, but it is only half-solution as this indexing process does not take into account the cronological order of date column and resulting this:Meanwhile, I managed to solve by sorting based on product ID & date at first before grouping.
