Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help: Clean List as Record in Field?

I have a very simple table where I've converted a delimited string of text into a list contained in a record (see screen shots).

 

Text String

Text String Converted to List

When I select the list record to view it, there are "blanks" (which is expected).

The quanitity of blanks will vary and the quantity of values may vary (less likely).

 

I was trying to write some M to dynamically clean the record list, so that in this example I would get it down to {13, 30}.

Here is the M I've written, it doesn't err out, but nor does it clean the list(s) for me... :-(.

I've also tried "" rather than " " to see if that would help and neither did anything.

let
    Source = #"SomeTable",
    #"Record to List" = Table.TransformColumns(Source, {"IdString", each Text.Split(_, ",")}),
    #"ReplaceValue" = Table.TransformColumns(#"Record to List", {"IdString", each List.ReplaceValue(_, " ", null, Replacer.ReplaceText)}),
    #"CleanNulls" = Table.TransformColumns(#"ReplaceValue", {"IdString", each List.RemoveNulls(_)})
in
    #"ReplaceValue"

 Suggestions much appreciated!

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYxRCsAwDEKvIvl2EGM36FlK73+NhW4ICu+Ba8WlzJq3gyETdDI2D5crszmh0QZ+flP+aArgieZZskYX+mK/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, IdString = _t]),
    Custom1 = Table.ReplaceValue(Source,each [IdString],each List.RemoveNulls(List.Transform(Text.Split([IdString],","),each try Number.From(_) otherwise null)),Replacer.ReplaceValue,{"IdString"})
in
    Custom1

     

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYxRCsAwDEKvIvl2EGM36FlK73+NhW4ICu+Ba8WlzJq3gyETdDI2D5crszmh0QZ+flP+aArgieZZskYX+mK/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, IdString = _t]),
    Custom1 = Table.ReplaceValue(Source,each [IdString],each List.RemoveNulls(List.Transform(Text.Split([IdString],","),each try Number.From(_) otherwise null)),Replacer.ReplaceValue,{"IdString"})
in
    Custom1

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    That works! Now I just need to understand why/how and where I went wrong, since it seems like I was on the right track.

     

    Thanks so much!

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      4 years ago

      Your approach is also right but you need to capture the unicodes of blanks and replace those blanks (these blanks are called whitespace characters and they are also other than what is "" in Excel and here and not only limited to ""). Rather than taking this route, I decided to take reverse route. Rather than checking for blanks, just check for the presence of numbers and replace anything other than numbers with nulls.