Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi All,
i want to create a calculates column with specific value when duplicates are found.
if the colum has duplicates give "duplicated"
ths formula i have used is:
Solved! Go to Solution.
You can use this in a Calculated Column @Anonymous
Duplicates =
VAR varCurrentValue = 'Sample'[Column1]
VAR varInstances =
COUNTROWS(
FILTER(
'Sample',
'Sample'[Column1] = varCurrentValue
)
)
var Result =
IF(
varInstances > 1,
"Duplicate",
"Unique"
)
RETURN
Result
You don't need ALL or CALCULATE. ALL in this context removes filters. Tables and Calculated Columns have no filter context, only row context.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou can try this:
Duplicates =
VAR varCurrentValue = 'Sample'[Column1]
VAR varInstances =
COUNTROWS(
FILTER(
'Sample',
'Sample'[Column1] = varCurrentValue
&& NOT 'Sample'[Column1]
IN {
"House",
"Table"
}
)
)
VAR Result =
IF(
varInstances > 1,
"Duplicate",
"Unique"
)
RETURN
Result
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingWorks as a charm!! Thanks!!
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRTmDSGUy6IJGuYNINSSWQHQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
Ad_DuplicateUnique = Table.AddColumn(Source, "Duplicate/Unique", each if List.Count(List.Select(List.Buffer(Source[Value]), (x)=> x = [Value])) > 1 then "Duplicate" else "Unique", type text)
in
Ad_DuplicateUnique
I would generally not recommend this in Power Query @danishefa as it would need to do a table scan. If it was a few hundred rows or perhaps low thousands, it might perform ok, but if more than that, even partitioning data Power Query bogs down, and it would be best done upstream in the source, or downstream in DAX.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHI,
this help worked. however, now i need an step worward.
is it possible to add into the formula exceptions??
for example give all duplicates except "house" & "table"
Regards
You can try this:
Duplicates =
VAR varCurrentValue = 'Sample'[Column1]
VAR varInstances =
COUNTROWS(
FILTER(
'Sample',
'Sample'[Column1] = varCurrentValue
&& NOT 'Sample'[Column1]
IN {
"House",
"Table"
}
)
)
VAR Result =
IF(
varInstances > 1,
"Duplicate",
"Unique"
)
RETURN
Result
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks @edhans,
just last question, how would you change the "in" for "contain" to avoid all the words containing "house"?
Thanks very much for your help
I would look at either CONTAINS or CONTAINSTRING
I'd need sample data to work with to test though. You might start a new thread. this one was solved almost a year ago and solved threads get less attention, so it is usually better to start a new thread with good sample data.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou can use this in a Calculated Column @Anonymous
Duplicates =
VAR varCurrentValue = 'Sample'[Column1]
VAR varInstances =
COUNTROWS(
FILTER(
'Sample',
'Sample'[Column1] = varCurrentValue
)
)
var Result =
IF(
varInstances > 1,
"Duplicate",
"Unique"
)
RETURN
Result
You don't need ALL or CALCULATE. ALL in this context removes filters. Tables and Calculated Columns have no filter context, only row context.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
17 | |
10 | |
9 | |
8 | |
8 |
User | Count |
---|---|
16 | |
15 | |
13 | |
12 | |
11 |