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 numb...
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".