Forum Discussion
DAX Identifying Duplicated Values
Dear All,
I have a PowerPivot table below.
You will see for Meter Point 1012351309356, with Filter "Consumptio" there is a duplicated Invoice_End at row 5 & 6 (10/05/2018). I would like to be able to flag the 1st and subsequent duplicated values in column Dup so I can treat values in that row differenctly in subsequent calcualtions.
Can you help?
Many thanks in advance!
| Meter Point | Filter | Invoice_End | Dup |
| 1012351309356 | Cancellati | 10/05/2018 | |
| 1012351309356 | Closing ba | 26/10/2018 | |
| 1012351309356 | Closing ba | 28/08/2018 | |
| 1012351309356 | Consumptio | 13/02/2018 | |
| 1012351309356 | Consumptio | 10/05/2018 | |
| 1012351309356 | Consumptio | 10/05/2018 | 1st Dup |
| 1012351309356 | Consumptio | 07/08/2018 | |
| 1012351309356 | Consumptio | 30/09/2018 |
Hi,
This M code works fine
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Meter Point", Int64.Type}, {"Filter", type text}, {"Invoice_End", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.ToText([Meter Point])&[Filter]&Date.ToText([Invoice_End])), Partition = Table.Group(#"Added Custom", {"Custom"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Meter Point", "Filter", "Invoice_End", "Index"}, {"Meter Point", "Filter", "Invoice_End", "Index"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Partition",{"Custom"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Dup", each if [Index] = 2 then "Dup" else null), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index"}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Columns1", {{"Invoice_End", type date}}, "en-IN") in #"Changed Type with Locale"Hope this helps.
5 Replies
Thanks Thomas.
How would I do it in query editor?
Hi,
This calculated column formula works
=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Meter Point]=EARLIER(Data[Meter Point])&&Data[Filter]=EARLIER(Data[Filter])&&Data[Invoice_End]=EARLIER(Data[Invoice_End])))>1,"Dup",BLANK())
Hope this helps.
Hi Ashish,
This is great thanks & works fine.
Is it possible to add an extra step that does not flag the first instance of the duplicate (let's call this the original value) only any subsequent duplicates?
This is because I want all the data from the row containing the original value, but only some of the data from the subsequent rows containing duplicated values.
Many thanks
Peter
Hi,
This M code works fine
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Meter Point", Int64.Type}, {"Filter", type text}, {"Invoice_End", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.ToText([Meter Point])&[Filter]&Date.ToText([Invoice_End])), Partition = Table.Group(#"Added Custom", {"Custom"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Meter Point", "Filter", "Invoice_End", "Index"}, {"Meter Point", "Filter", "Invoice_End", "Index"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Partition",{"Custom"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Dup", each if [Index] = 2 then "Dup" else null), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index"}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Columns1", {{"Invoice_End", type date}}, "en-IN") in #"Changed Type with Locale"Hope this helps.
