Forum Discussion

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.

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

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