Forum Discussion

e116736's avatar
e116736
Frequent Visitor
3 years ago

Need help to Identify Duplicate field

I am trying to find a formula which Identifies the duplicate address and makes it a 1 but keeps the original instance of that address as a 0. 
Need Help
e116736's avatar
e116736
Frequent Visitor
3 years ago

CustomerID  Customer Address JobType Expected Result

12                    40 Main St             Install           0

13                     11 Water St           Install           0

14                    40 Main St             Verify            1

The goal is to capture the jobs where we went to an address more than one time and exclude those jobs from the totals using the filters on the dashboard

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
3 years ago

Hi,

This M code works.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Partition = Table.Group(Source, {"Customer Address"}, {{"Partition", each Table.AddIndexColumn(_, "Index",0,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"CustomerID", "JobType", "Index"}, {"CustomerID", "JobType", "Index"})
in
    #"Expanded Partition"

Hope this helps.

  • e116736's avatar
    e116736
    Frequent Visitor
    3 years ago

    Thank you. Is there another version of this formula which can be used on the data view/data table side vs the power Query side. Thank you

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      3 years ago

      Hi,

      Use this calculated column formula

      Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer Address]=EARLIER(Data[Customer Address])&&Data[CustomerID]<EARLIER(Data[CustomerID])))+0

      Hope this helps.