Data Transformation Challenge

[Anonymous]

Hello Forum,

I'm trying to see if I can get help with a data transformation scenario.  I have a data table with 3 columns.  Employee, Date/Time and Weight.  Sometimes the inputs can go into the wrong column.  See attached example:

I was hoping to copy my data source in my model.  Filter each query down to the rows that conform to one input method and then merge the queries back together with the data corrected.  Trouble is I can't find the best way to filter the data in each query to isolate the input method.  Maybe there is a better strategy that I'm not thinking of.  Any thoughts on how to accomplish are appreciated!

[Anonymous]

I tried that before posting.  I was hoping to use the remove error rows functionality.  Changing the data type of the column only changes the presentation of the data.  It doesn't error.  Does anyone know how to force an error instead of converting underlying data to the chosen data type? 

[Nathaniel_C · Accepted solution]

Hi @Anonymous ,

I remembered seeing this, and found it in M is for (Data) Monkey, a very useful book. @KenPuls is one of the authors.

So add a custom column with the formula below in the picture in PQ. You get the result in the picture.  You can then go to filter for that custom column and unfilter null.  That takes away the rows that have bad data.  You may want to copy those rows first, so that you could change your original data - you could sort by ascending, and the nulls float to the top.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

Thank you so much @Nathaniel_C !  Exactly what I was looking for.

---

[Nathaniel_C]

Hi @Anonymous ,

I believe when you bring in the data to Power Query, it will signify an error if the type does not match the column type. Then you can either fix the data, or filter out errors in each  column. It is pretty cool. You may have to adjust the type first as in your date column. Also your emp id is probably better as text.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel