Jump to content

Calculate column to eliminate duplicate values

christopher sawtelle 2

Recommended Posts

Heres what I am trying to do: I have a Date column and a column with production values, one value per month. In this column the dates (i.e. Jan-Dec for 2012) are repeated several times and therefore so are the production values. I am attempting to create a calculated column that will give me these production values but only one time for each month. In other words only one occurrence of Jan, Feb, Mar etc.


Thanks for any thoughts.

Link to comment
Share on other sites

Add a calculated column "Rank" with formula rank(rowid(),[Date],[Volume]). This will place a value of '1' next to the first unique combination of Date and Volume. You can then use a filter in your visulization remove the non-'1' (repeats) or add another calculated column "NewVolume" with formula if([Rank]=1,[Volume]). All repeat rows will be empty and will not show up. Then use [Date] and [NewVolumne] in your visualization.


Rank function


Link to comment
Share on other sites

  • 2 years later...
  • 3 years later...

Sometimes I add a pivot and just transfer the rest of the rows

Select Insert > Transformations...

Added transformations

Transformation name: Pivot

Row identifiers: Unique ID

Value columns and aggregation methods: (None)

Column titles: (None)

Column naming pattern: %C

Transfer columns and aggregation methods: First(Production Date) First(Region) First(Operator)

Transfer column naming pattern: %T

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...