Forum Discussion

himynameisjuan's avatar
himynameisjuan
Frequent Visitor
2 years ago
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...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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
    v2
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    I 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

     

     

     

jgeddes's avatar
jgeddes
Super User
2 years ago

For a) you can use Table.TransformColumns and check if the character in the first position is a letter or number. The code below is an example.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTwLyjJzM9TcFSK1YlWcoJxjcBcZxg3GcxVhHFNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    changeDataType = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    Custom1 = Table.TransformColumns(changeDataType, {{"Column1", each if Text.PositionOfAny(_, {"0".."9"}) = 0 then "Starts With A Number" else if (Text.PositionOfAny(_, {"A".."Z", "a".."z"})) = 0 then "Starts With A Letter" else "What does it start with?"}})
in
    Custom1

 

For b) you can split the column into rows by comma delimiter and then extract the text after the second space delimiter for rows that start with a space. The code below is and example.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtRT8C8oyczPU3DUUTCCc5x0FMwRUs5KsbEA", 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}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Split Column by Delimiter", {{"Column1", each if Text.StartsWith(_, " ") then Text.AfterDelimiter(_, " ", {1, RelativePosition.FromStart}) else _, type text}})
in
    #"Extracted Text After Delimiter"
dufoq3's avatar
dufoq3
Community Champion
2 years ago

himynameisjuan, jgeddes found great solution for 2nd question, but it is not easy to find a pattern. It will be even harder if you add 2 more value i.e.:

1. Option A, 2. Option B, 71. Option C, 100. Option D,  102. Option E

and you would like to have outcome like this:

Column1: 1. Option A
Column2: Option B
Column3: Option C

Column4: 100. Option D

Column5: Option E

 

What I'm trying to tell is that to do this there has to be such pattern. If there is a pattern, than there are usually many ways.