Forum Discussion

PMF998's avatar
PMF998
Regular Visitor
7 years ago
Solved

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 b...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

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.

 

PMF998's avatar
PMF998
Regular Visitor
7 years ago

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

 

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    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.