cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nanaki
Helper II
Helper II

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 notesLiterDelivery notes without dublicateLiters
123100123100
1245012450
1253012530
123100blankblank
12450blankblank
126200126200
127250127250
123100blankblank
12530blankblank

 

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

1 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])

4.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

10 REPLIES 10
v-diye-msft
Community Support
Community Support

Hi @Nanaki 

 

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

1.PNG

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.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

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 @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])

4.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Hi @v-diye-msft ,

 

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

 

🎉

 

Best regards 

Greg_Deckler
Super User
Super User

@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())


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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.

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


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Hi @Greg_Deckler,

 

yes i can.

amitchandak
Super User
Super User

@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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

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])

Unbenannt.PNG

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors