Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Infinite Data Duplication

Hi! I have a dataset that is structured like this:

  1. Name
  2. Social Security Number
  3. Address Line 1
  4. Address Line 3 
  5. Tax information
  6. Tax information
  7. Tax information 
  8. Blank row
  9. Name
  10. 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

  • Anonymous's avatar
    Anonymous
    Not 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