Split a column and sort

Hi Anonymous ,

 

You can do this in Query Editor, check the query below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwUIrVAdN6CJYhnGUJZcFEDOGqDPUQYkZKsbEA", 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}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Column1 - Copy", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1 - Copy.1", "Column1 - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1 - Copy.1", type text}, {"Column1 - Copy.2", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [#"Column1 - Copy.2"] <> null and [#"Column1 - Copy.2"] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1 - Copy.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1 - Copy.1", "Column1"})
in
    #"Reordered Columns"

You will need to dumplicate the column first then use split column feature then remove empty rows and at lase remove extra cloumn.

Pbix as attached.

 

Best Regards,

Jay