The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
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
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.
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
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.
Hi,
Share some data to work with and show the expected result.