How to clean rows based on a condition?

[Anonymous]

I am beginner and using Olist Store database. I am trying to clean the data by making a new column to clean errors where the delivered time is before the shipment or approved time. My plan was to make a condition column where I made the condition so that if delivered date is before the shipped time then assign it to "Error" and if not then assign it"correct" and then filter it from there. However, all of the columns are were assigned to "correct" which is not true. 

I check if I used condition column incorrectly by making a smaller table and testing it on that and it worked. 

Why does it not work? Or is there another way for me to clean the data? 

[Anonymous]

Hi @Anonymous ,

Not sure, here are my steps, hope it helps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lEy0jeCMGN1ohEcHYSS2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"delivered date" = _t, #"shipped time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"delivered date", type datetime}, {"shipped time", type datetime}}),
    Custom1 = Table.SelectRows(#"Changed Type",each [delivered date] >= [shipped time])
in
    Custom1

You can create a new blank query and paste the code into the advanced editor.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[HotChilli]

It's not possible to say why it doesn't work without seeing the data and the code used to process it.

However, you are in the best position to debug it (especially because you have a working sample to compare).

So, check the data types, write test columns to identify the issues, for example, you say all the rows were 'correct' so try editing the code to change the comparison to the opposite ('<' instead of '>', maybe) and see what that does and then investigate. 

And compare the 'real' data with the sample data. There must be something different.

edit:

Also there can sometimes be unusual results if the power query code folds back to the database.  I don't know what connection type is being used here to the Olist db but you can investigate that too (see query folding)