Help: Clean List as Record in Field?

[Anonymous]

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!

[Anonymous]

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]

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. 

[Vijay_A_Verma · Accepted solution]

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