Removing duplicate data

[panipat1990]

Dear Team,

Kindly Help me For Removing Duplicate Data.

Date	Sale	Art
01-04-2018	100	A
02-04-2018	200	B
03-04-2018	300	C
04-04-2018	400	D
05-04-2018	500	E
06-04-2018	600	F
01-04-2018	400	A

In Report Making 01-04-2018, It Show Only 400 Sale means Updated Records..How to Show

Kindly Help me

I read Many Thread And i Saw Below Steps But i am not understanding how to apply thses Steps

1. Use CONTAINS function to get the records which both contain the two keys.(duplicate records)
2. Filter inactive records from above records.(inactive records of duplicate records)
3. Use EXCEPT function to exclude the inactive records from original records, then you will get the active records and unique records which you want.

[Floriankx]

If your aim is to have 500 as Output you just need to SUM(Table[Sales]).

As long as you don't need any extraordinaries this should do the job.

If not please give as further information how your result should look like.

Best regards.

[panipat1990]

I know it . ..but my requirement is if user select 01-04 then it show 400 . ..means value should be replaced . ...I did another tool . .I used for getting this code . ..where not exists ()...
but in power bi I do not know . ........I saw three steps . .I sent already . .but how these steps applied . ....kindly help me

[panipat1990]

Anyone know this that how to show update record . ....

[panipat1990]

Kindly Tell me

[panipat1990]

Dear Xiaoxin Sheng,

How to apply these three steps...Kindly tell me 

[ImkeF · Accepted solution]

In the query editor, you add an index-column:

Check the new index-column and sort descending:

Edit the formula in the formula bar to this (otherwise the sort-order will not be kept):

Check columns "Date" and "Art", and remove duplicates:

This is the code that you can paste into the advanced editor to follow the steps along:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc6xDcAgDETRXVyDZINBtCQhSyD2XyPWWXKR5opXnP7exJJZswxKJMy2k04yLsEFfDnX4Aq+nTVYwY9zC27g5dyDO/h1lt+JlZwP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Sale = _t, Art = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sale", Int64.Type}, {"Art", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Index",{{"Index", Order.Descending}})),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Date", "Art"})
in
    #"Removed Duplicates"

[panipat1990]

Hi,

When i load Next 4-Feb-2018 Value 

Date                       Sale               Art

4-Feb-2018            900                B

After Refresh it should be 900 Sale means update record..

But it is showing 200..you Sent me it is working only first row..But i want to update any row..Kindly Help Me

[panipat1990]

Sorry It's Working.Thanks

[panipat1990]

Hi,

How can i make a composite key in power Query editaor Means Date&'-'&Art    01-04-2016&'-'&A

How can i make..Kindly help me