Copy unique Numbers

[Nanaki]

Hi,

I got a problem.
I have a table with a few columns.
my table has more than 300.000 lines.
In one column are delivery notes, many of them exist more than one time in the column.
I want to create a new column where the unique delivery notes are copied.
For Example: 

Delivery notes	Liter	Delivery notes without dublicate	Liters
123	100	123	100
124	50	124	50
125	30	125	30
123	100	blank	blank
124	50	blank	blank
126	200	126	200
127	250	127	250
123	100	blank	blank
125	30	blank	blank

Could you help me to copy the delivery notes, with a function, only one time from column 1 to column 3?

Thanks in advance!

Best regards

Lennart

[v-diye-msft]

Hi @Nanaki 

Plz let me know if you'd like to get below one:

Measure = var a = COUNTROWS(FILTER(ALL('Table'),[Delivery notes]=MAX('Table'[Delivery notes])&&[Index]<=MAX('Table'[Index])))
return
IF(a =1 , MAX('Table'[Delivery notes]),BLANK())

Measure 2 = IF([Measure]=BLANK(),BLANK(),MAX('Table'[Liter]))

Pbix attached.

[Nanaki]

Hi @v-diye-msft,

thank you for your reply.
With this solution I only get a table, right?, but what i need is a extra column with the delivery notes in my data.

Later i need to relate data to the new delivery note column.

[v-diye-msft · Accepted solution]

Hi @Nanaki 

Let me know whether this helps:

Column = var a = COUNTROWS(FILTER(ALL('Table'),[Delivery notes]=EARLIER('Table'[Delivery notes])&&[Index]<=EARLIER('Table'[Index])))
return
IF(a =1 , 'Table'[Delivery notes],BLANK())

Column 2 = IF([Column]=BLANK(),BLANK(),[Liter])

[Nanaki]

Hi @v-diye-msft ,

yes! that worked perfectly.
Thank you very much, to all of you! 

🎉

Best regards 

[Greg_Deckler]

@Nanaki Do you have an Index or something to denote "before"? Do you want DAX or Power Query or doesn't matter? If DAX and you have an index or date, you could use a variation of MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  IF(ISBLANK(__Previous),__Current,BLANK())

[Nanaki]

Hi @Greg_Deckler thank you for your replay as well.

No i dont have an index. I have column with date but every date exist more than 500 times.
It doesn't matter how this will be fixed. But i cant find the relation between your solution and my table.

[Greg_Deckler]

@Nanaki Can you just add an Index column in Power Query Editor? Need something to define "previous"

[Nanaki]

Hi @Greg_Deckler,

yes i can.

[amitchandak]

@Nanaki ,

In case you need a column you need have index column

and try this formula

if(isblank(countx(filter(Table,[Delivery notes] =earlier([Delivery notes]) && [Index] <=earlier([Index])),[Delivery notes])), [Liter], blank())

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

[Nanaki]

Hi @amitchandak,
thank you for your reply.
Do i have to relate the Index column to my Delivery notes column?
How is a Index column working? 
I just created a Index column and tried your formula but the cells in the column with the formula are all blank.

You can see below what it looks like.

Einzelne LS = if(isblank(countx(filter('Alle Werte','Alle Werte'[Alle Lieferscheine] =earlier('Alle Werte'[Alle Lieferscheine]) && [Index] <=earlier([Index])),'Alle Werte'[Alle Lieferscheine])),'Alle Werte'[Alle Lieferscheine])