Forum Discussion
Infinite Data Duplication
Hi! I have a dataset that is structured like this:
- Name
- Social Security Number
- Address Line 1
- Address Line 3
- Tax information
- Tax information
- Tax information
- Blank row
- Name
- Etc.
I'm trying to create a cumulative index that tags each piece of information with its corresponding number (so all names are tagged as 1s, all socials are tagged as 2s, etc.) That way I can categorize each piece of information automatically.
The custom column formula I'm using to do this is as follows, where the Helper column lists a 1 for every non-blank cell in Column1, and Index is simply an Index:
let
Source = #"Added Index",
AddedCumulative = Table.AddColumn(Source, "Cumulative", each
let
currentIndex = [Index]
in
if currentIndex < 251 then
let
allRows = Table.FirstN(Source, currentIndex + 1),
reversedRows = List.Reverse(allRows[Helper]),
indexOfNull = List.PositionOf(reversedRows, null),
cumulativeValue = if indexOfNull = -1 then List.Count(reversedRows) else indexOfNull
in
cumulativeValue
else
null
),
RemovedIndex = Table.RemoveColumns(AddedCumulative, "Index")
in
RemovedIndex
I should only have 251 rows of data - but this formula is duplicating my data, so in row 252, my dataset restarts. This happens over and over again - my dataset just repeats. On the bright side, my index is working - but the query isn't functional because of all the extra rows. Does anyone know how to fix this?
1 Reply
- AnonymousNot applicable
You can start by adding the index (start with 1, not zero), and then add a column with each Number.Mod([Index], 11).
--Nate