Cleaning contaminated data

Jack007 — Fri, 25 Aug 2023 03:22:38 GMT

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 num	Customer name	Order Date
Aaaa	Jack	2021/1/1
BBBB	Jack	2021/1/2
BBBB/178451=	#### ####	2021/1/3
Aaaa_46464646	?????	2021/1/4

Order num	Customer name	Order Date
Aaa	Jack	2021/1/1
BBBB	Jack	2021/1/2
Aaa	Jack	2021/1/3
BBBB	Jack	2021/1/4

Re: Cleaning contaminated data

lbendlin — Thu, 24 Aug 2023 23:11:56 GMT

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

topic Re: Cleaning contaminated data in DAX Commands and Tips

Cleaning contaminated data

Re: Cleaning contaminated data