Nlp data cleaning problem.

Hi Anonymous ,

According to your description, here's my solution.

1.This is the sample data.

2.Select column B,C,D,E at the same time, then replace  value " with )

Get this result.

3.Select extract text after delimiter in the Transform tab.

Get this result.

4.Seperately select column B,C,D,E, then select split column in the Transform tab

 

Get this result.

5.Remove the null columns, get this result.

6.Select column B,C,D,E at the same time, and then select merge columns in the Transform tab.

Get the expected result.

This is the query code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc0xDsMgDAXQqyBPsZSl7SmydyMMyJjUKmApoJ6/QLK9768vWwub8RlWWB54qAYT9TQjPpE0SzmGX6gxCnE3uNXC+zO4CAbf/JSg1AuClXxrfHKYhx9KMT4lQ5xSnfOtFzsU5rDDZNXMN0t/c5OS0Lf24Nwf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","""",")",Replacer.ReplaceText,{"B", "C", "D", "E"}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Replaced Value", {{"B", each Text.AfterDelimiter(_, ")"), type text}, {"C", each Text.AfterDelimiter(_, ")"), type text}, {"E", each Text.AfterDelimiter(_, ")"), type text}, {"D", each Text.AfterDelimiter(_, ")"), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text After Delimiter", "B", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"B.1", "B.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"B.1", type text}, {"B.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "C", Splitter.SplitTextByDelimiter(")", QuoteStyle.Csv), {"C.1", "C.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"C.1", type text}, {"C.2", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "D", Splitter.SplitTextByDelimiter(")", QuoteStyle.Csv), {"D.1", "D.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"D.1", type text}, {"D.2", type text}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3", "E", Splitter.SplitTextByEachDelimiter({")"}, QuoteStyle.None, true), {"E.1", "E.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"E.1", type text}, {"E.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type4",{"B.2", "C.2", "D.2", "E.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"A", "B.1", "C.1", "D.1", "E.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.