Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Data Transformation Challenge

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:

example.png

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!

1 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

Error function 2.PNG

 

Error function.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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? change.png

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

Error function 2.PNG

 

Error function.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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


Nathaniel_C
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.