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

     

     

     

himynameisjuan's avatar
himynameisjuan
Frequent Visitor
2 years ago

Hello, thank you very much dufoq3 jgeddes for the great responses. 

For (2) It would be nice not to trust in the number of spaces (one or two spaces) or the number of integers (1 or 11 or 111). As you pointed out, the ideal approach would be one that dynamically adapts regardless of the number of spaces or numbers. 

Doesn't Power Query have a means to find and separate into columns numbers from text and vice versa?

Maybe running a Python or R script for (2) would more dynamic?

 

Thank you very much, kind regards

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi himynameisjuan, you're welcome.

     

    It is possible to do such split also in PQ, but it is useless for sample data you provided.

     

    Column1 splitted:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKNjQyTklNU4rViVZydHIG8lxc3ZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_Separated = Table.AddColumn(Source, "Separated", each 
        [ a = Splitter.SplitTextByCharacterTransition((x)=> not List.Contains({"0".."9"}, x), {"0".."9"})([Column1]),
          b = List.Combine(List.Transform(a, Splitter.SplitTextByCharacterTransition({"0".."9"}, (x)=> not List.Contains({"0".."9"}, x)))),
          c = Text.Combine(b, "|")
        ][c], type text),
    Ad_SplitCount = Table.AddColumn(Ad_Separated, "SplitCount", each Text.Length(Text.Select([Separated], "|")) +1, Int64.Type),
    SplitColumnByDelimiter = Table.SplitColumn(Ad_SplitCount, "Separated", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"1"..Text.From(List.Max(Ad_SplitCount[SplitCount]))} ),
    RemovedColumns = Table.RemoveColumns(SplitColumnByDelimiter,{"SplitCount"})
in
    RemovedColumns