Adding an Index Number If

[hatstand]

Hi All,

I have imported a csv file that is many files added together. So every time the data changes to a new file its says “File1” in one of the columns. What I am trying to do is add a column that adds an index to these names, so I can then do a Fill up and Down. This would give each file the same index number which I can use in Pivots etc.

Any ideas, I feel like I’m so close but can’t quite get it.

 

[RoryA]

If you don't need them to be sequential (just the same for each file), you could add an index column as normal, then add another column that returns the index number if there is "File1"in the relevant column, or null otherwise. Then Fill down that new column.

 

[hatstand]

Thanks. I was thinking about that. Ideally it would be sequential so I could give them names. I have managed to get something working using 'List.Sum(List.FirstN(#"Added Index"[Number],[Index]))'. Unfortunately it is very very slow. But I think I'm going in the right direction. Cheers.