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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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