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

6 Replies

  • 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
      Super 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