Forum Discussion

Jack007's avatar
Jack007
Frequent Visitor
3 years ago

Cleaning contaminated data

I have a dataset with about 8 million rows with both customer names and order numbers contaminated.  

 

I can clean it manually by searching for the customer name and getting the correct order number(Aaaa).  Then I filter rows that contain text Aaaa. Replace empty cells with Jack, can anyone help me automate the process?

 

Order numCustomer nameOrder Date
AaaaJack2021/1/1
BBBBJack2021/1/2
BBBB/178451=####  ####2021/1/3
Aaaa_46464646?????2021/1/4

 

Order numCustomer nameOrder Date
AaaJack2021/1/1
BBBBJack2021/1/2
AaaJack2021/1/3
BBBB Jack2021/1/4

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    Learn about ReplaceValue and FillDown.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxMTFTSUfJKTM5WitWB8ONNzCAQKKEUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order num" = _t, name = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"name"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each Text.StartsWith([Order num], "Aaa")),
    #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows",each [Order num],each Text.Start([Order num],3),Replacer.ReplaceValue,{"Order num"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value1",{"name"})
in
    #"Filled Down"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".