I would like to add column that would be kind of counter for the values that are repeating. For example, if number 1000 repeats 3 times in the column, the first instance of 1000 would be 1, second instance of 1000 would be 2 and third instance of 1000 would be 3, and so on. If there are 5 lines of number, let's say, 2000 it should go from 1 to 5. Is it possible to do this with power query or I would have to use excel formulas or vba. I prefer to use power query.
I appreciate help. Thank you in advance.
Group your data and keep all rows. Call the new grouped column "Data"
Add a column
=Table.AddIndexColumn([Data],"Index",1,1)
Delete the grouped column
Expand the new column. It will number the rows as you have requested.
Thanks Alan so much for your answer. I encountered the following error message.
Expression.Error: We cannot convert the value "0000001" to type Table.
Details:
Value=0000001
Type=[Type]
Hi Amir,
You haven't provided a sample of the data, or your query, so it's nearly impossible to tell you what is going wrong.
If you look at the attached file, I've written a query that does what you want. Here's the M code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "TempIndex", 0, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Values"}, {{"Data", each _, type table [Values=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Values", "Data"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Values", "TempIndex", "Index"}, {"Values", "TempIndex", "Index"}),
#"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"TempIndex", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"TempIndex"})
in
#"Removed Columns1"
Regards
Phil
Thanks Philip. I think my source data was messed up. Is this possible to do with Excel formula?
Hi Amir,
You might be able to do this with formulae, I hadn't thought about it as you asked for a solution with Power Query?
Regards
Phil
I can count this as resolved. Thanks Philip so much for posting your workbook. After I followed your steps I found where my mistake was. Thanks also to Alan who posted his answer as well.
