Auomated process for the Data cleaning

[gurudayalsahu]

Hi,

I am looking for some type of query or scripting that might help during data cleaning or removing a particular records from a data set. I was exploring the option but not able to find it,

The scripting might be helpful to be used in other data set as well.

Thanks in advance .

Sahu

[Anonymous]

«Vaste programme...» if you don't give more specific specs!

[gurudayalsahu]

Thanks for the reply.

There are more than 4 Tables in the current dataset, where we wanted to get all  records to be removed based on a column name "user_role" having values  as "Test" avaiblle in one of the table named "mobileusers", while all the remaining tables are connected with a column name "User_name"

We wanted a have some auotomated process that should help us to get all those test entries removed and should not appear in the existign calculations.

Thanks once again for your further suggestion on this.

With best,

Sahu

[Anonymous]

peraphs this function could be useful

Table.RemoveMatchingRows(table as table, rows as list, optional equationCriteria as any) as table

But let me to insist, that if you want a specific help you have to give a specific spec.

You should give some example tables of your input data and describe what you expect, perhaps using a table in addition to the verbal description.

[gurudayalsahu]

Thanks you both for the reply.

I am sharing a data Just to share further details on this requirement.

Units Sold	M_Price	S_ Price	G_Sales	Sales	Profit	Date	User_Name
1618.5	$       3.00	$    20.00	$   32,370.00	$  32,370.00	$  16,185.00	1/1/2014	pinki
1321	$       3.00	$    20.00	$   26,420.00	$  26,420.00	$  13,210.00	1/1/2014	test
2178	$       3.00	$    15.00	$   32,670.00	$  32,670.00	$  10,890.00	6/1/2014	pinki
888	$       3.00	$    15.00	$   13,320.00	$  13,320.00	$    4,440.00	6/1/2014	sunita
2470	$       3.00	$    15.00	$   37,050.00	$  37,050.00	$  12,350.00	6/1/2014	manisha
2179	$       3.00	$    15.00	$   32,670.00	$  32,670.00	$  10,890.00	6/1/2014	pinki
888	$       3.00	$    15.00	$   13,320.00	$  13,320.00	$    4,440.00	6/1/2014	sunita

In the above example, we wanted to remove the data from the user named "Test" and also repeated data.

Thanks,

Sahu

[Anonymous · Accepted solution]

here the step to remove duplicare rows and filter out unwanted rows

here the code producted bu GUI in the advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3ZHNCsIwEAZfJQSPS91v89tnKT30IBjEIrS+v8GqpKlCz+aU2ctkNl2n4REbp0mrg1qOaZg/LFyQETKhGNQMT4huYRxxFIbN11saL0n3lGVGsE8lnmw5qBmGBLxRzadpfpoEIf40wa2jfBW1YjDF9sX+W1SMO035zaZqWLFSlqzdqqb7mOZhybKBd2YFYldmVYz8d27rug5jms7De4ftv+2wfwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Units Sold" = _t, #" M_Price " = _t, #" S_ Price " = _t, #" G_Sales " = _t, #"  Sales " = _t, #" Profit " = _t, Date = _t, User_Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Units Sold", Int64.Type}, {" M_Price ", type text}, {" S_ Price ", type text}, {" G_Sales ", type text}, {"  Sales ", type text}, {" Profit ", type text}, {"Date", type date}, {"User_Name", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([User_Name] <> "test"))
in
    #"Filtered Rows"

[amitchandak]

@gurudayalsahu , refer if this can help https://social.technet.microsoft.com/Forums/en-US/acb06cd7-f0c7-44f9-8b7e-5e74ffd32780/remove-rows-w...