Forum Discussion
Copy unique Numbers
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
Hi Anonymous
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])
10 Replies
Anonymous ,
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
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])
Anonymous 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/339586.
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())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.Anonymous Can you just add an Index column in Power Query Editor? Need something to define "previous"
Hi Anonymous
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.
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.
Hi Anonymous
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])
