Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello guys,
I'm trying to create a custom column in my 'investigation' table. I want that this new column tells me if there are some duplicates ERP number in my 'source' table matching with ERP number in my 'investigation' table. How can i do that ?
Best regards,
ScrappyS
Solved! Go to Solution.
See this formula:
#"Add Duplicate Check" =
Table.AddColumn(
#"Changed Type",
"Duplicate Check",
each
let
varCode = [Code],
varColor = [Color]
in
if Table.RowCount(
Table.SelectRows(
#"Source Table", each [Code] = varCode and [Color] = varColor
)
) > 1
then true
else false,
type logical
)
My PBIX file is here. It looks in this table:
and finds duplicates as shown:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe following formula will tell you if the Code in your current table exists in the Code column of the Source Table.
List.Contains(#"Source Table"[Code], [Code])
You can see my sample PBIX file here.
If you need further help, please provide actual data.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello Edhans,
Thanks for the answer, but how can i get only the ones in 'source' [code] with an occurence > 1 AND match to my 'investigate'[code] ?
Best regards,
ScrappyS
again, provide sample data, with some examples of expected output. Please don't expect us to come up with sample data. Then we get it wrong, which is what I did with my first try.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMy bad, i can't provide pbix file for now but maybe with this schema, it will be more precise :
Thank you very much !
See this formula:
#"Add Duplicate Check" =
Table.AddColumn(
#"Changed Type",
"Duplicate Check",
each
let
varCode = [Code],
varColor = [Color]
in
if Table.RowCount(
Table.SelectRows(
#"Source Table", each [Code] = varCode and [Color] = varColor
)
) > 1
then true
else false,
type logical
)
My PBIX file is here. It looks in this table:
and finds duplicates as shown:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks a lot, it worked like a charm !
Great @Anonymous . Glad to help out.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.