Forum Discussion
Solved
Data Cleansing Questions
Hello. When using Power Query to cleanse data from a table, before normalizing it, I wonder how to do the following please: (a) How is it possible in Power Query for a given column, to perform a...
Hi himynameisjuan, answer to question no. 1
Result
let Source = #table({"Data"}, {{1}, {"A"}, {25}, {"B"}, {"10"}}), v1 = Table.AddColumn(Source, "v1", each if [Data] is number then "Number" else "Text", type text), v2 = Table.AddColumn(v1, "v2", each if (try Number.From([Data]) otherwise [Data]) is number then "Number" else "Text", type text) in v2I have modified my code to
- Split the strings into columns
- Adjust for any number of spaces
- The existing code already accomodated any number of digits
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtRT8C8oyczPU3DUUTCCc5x0FMwRUs5KsTrRSqYIgWQdBUMTOC8CyLOA86LAiokzF6jRwADOddFRAPIRil2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), //Use regex to split #"Run Python script" = Python.Execute("dataset['Split'] = dataset['Column1'].str.split(r',\s+\d+\.\s+')#(lf)",[dataset=#"Changed Type"]), //remove unneeded columns and expand the table #"Removed Columns" = Table.RemoveColumns(#"Run Python script",{"Name"}), #"Expanded Value" = Table.ExpandTableColumn(#"Removed Columns", "Value", {"Split"}), //Replace single quote with double quote to create a valid Json #"Replaced Value" = Table.ReplaceValue(#"Expanded Value","'","""",Replacer.ReplaceText,{"Split"}), //Convert the Python array to an M List #"To List" = Table.TransformColumns(#"Replaced Value",{"Split", Json.Document}), //Max number of Columns numCols = List.Max(List.Transform(#"To List"[Split], each List.Count(_))), #"Extracted Values" = Table.TransformColumns(#"To List", {"Split", each Text.Combine(List.Transform(_, Text.From), ";"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Split", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), List.Transform({1..numCols}, each "Split." & Text.From(_))) in #"Split Column by Delimiter"Source Data
Results
For splitting on your complex delimiter, you can use Regular Expressions.
I've not used Python much in Power Query, so there may be more efficient methods, but this does work on your data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtRT8C8oyczPU3DUUTCCc5x0FMwRUs5KsTrRSqYIgWQdBUMTOC8CyLOA86KUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
//Use regex to split
#"Run Python script" = Python.Execute("dataset['Split'] = dataset['Column1'].str.split(r',\s\d+\.\s*')#(lf)",[dataset=#"Changed Type"]),
//remove unneeded columns and expand the table
#"Removed Columns" = Table.RemoveColumns(#"Run Python script",{"Name"}),
#"Expanded Value" = Table.ExpandTableColumn(#"Removed Columns", "Value", {"Split"}),
//Replace single quote with double quote to create a valid Json
#"Replaced Value" = Table.ReplaceValue(#"Expanded Value","'","""",Replacer.ReplaceText,{"Split"}),
//Convert the Python array to an M List
#"To List" = Table.TransformColumns(#"Replaced Value",{"Split", Json.Document}),
//Expand the list to new rows (could expand to columns if preferred
#"Expanded Split" = Table.ExpandListColumn(#"To List", "Split"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Split",{{"Split", type text}})
in
#"Changed Type1"Source
Results