excel power query: how to convert string "abc 123" into a table with abc above and 123 bel-CodePudding

How can I convert the string "abc 123" into a table with abc above and 123 below using Excel Power Query?

So the output would be a table like this, where the top is a string and the bottom is a float/ decimal number.

abc
123

It doesn't need to be a heading, that's just the table formatting being applied here.

CodePudding user response：

Not clear what you want but this converts a string to a table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVjA0MlaKjQUA", 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}}),
    #"Extracted First Characters" = Table.TransformColumns(#"Changed Type", {{"Column1", each Text.Split(_," "), type text}}),
    Column1 = #"Extracted First Characters"{0}[Column1],
    #"Converted to Table" = Table.FromList(Column1, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

CodePudding user response：

You could split the column into two at the space as a delimiter, then pivot without aggregating (this code also defines an example table in the first place):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVjA0MlaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Changed Type"[Column1.1]), "Column1.1", "Column1.2")
in
    #"Pivoted Column"

Alternatively, you could transpose the data then promote the first row to become the column headers. This has the advantage of also being extendable to multiple columns > rows for example "abc 123 456":

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVjA0MlYwMTVTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"