Forum Discussion
how to identify wrong data entry in a column compared to majority of records for the same referenc
I have in column A materail description and in column B - melting temperature.
There are hundreds of rows in the table. What I need is to find a way in additional column to have a marker "false" when there is a different melting temparature comparing to majority of the records for that specific material.
5 Replies
Sample data?
I think you can use List.Mode() in Power Query to get the mode values in a column
I am placing link to the sample data https://docs.google.com/spreadsheets/d/1jvQAOrrQZVumTxGvv3sy8kIVyDEp24hl/edit?usp=share_link&ouid=108277364819069481385&rtpof=true&sd=true
Great, Thanks.
Why is there no pointer next to 39 for Material1?
In fact there should be a pointer as it is outside 40. your comment is correct
I think the first step (in Power Query) would be to produce a summary table with 2 columns (each Material and the Mode value for that material).
Do a 'Group By' on material and pick the Min aggregation on the temperature column.
We will edit this step in the formula bar now.
Change List.Min to List.Mode.
You should now have a 2 column table.
--
You can Merge this back to the original table (Inner Join) to bring back the Mode Value as a column.
--
Then add a custom column to compare the melt temperature with the Mode value and return "tick" or something.
--
