CODEWITHSUNDEEP
excelpowerbipowerquerym
Thoughtful_Giraffe
Thoughtful_Giraffe

Reputation: 81

Split Column Without Converting to Text in Power Query?

Initial table

Is there a way to split my cell into multiple rows without converting my datatypes to text? Using the "Split column" into rows feature in the Power Query ribbon converts the entire column to text and I lose the datatype values that I want. I've tried converting the column back to type 'any' but each cell remains as text.

After splitting

Upvotes: 1

Views: 830

Answers (1)

TheRizza
TheRizza

Reputation: 2062

Even though you are showing different types in the query, when the query is loaded it will all be loaded as text (Excel and Power BI).

Excel data. It's all text!

The format of your table looks like strongly typed columns that were Unpivoted. If you pivot the data back into proper columns with a single type, this becomes simple.

In the query below, the first three steps get your sample table back into proper columns with a single type per column. Table with proper columns with single type

Then I'm just splitting by delimiter into rows and then setting the type.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKuMa4xNFDSUfLJLC5RitWJVjI2AfL8SnMNwTywHJBnBOYl5ZSmAvnO+Tn5RVBpfWN9Q0tLC6CoS2JJqlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, Column = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Column]), "Column", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Num1", Int64.Type}, {"Num2", type number}, {"Date", type date}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"List", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "List"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"List", Int64.Type}})
in
    #"Changed Type1"

Result of split by rows

If you need the data split by columns, it looks like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKuMa4xNFDSUfLJLC5RitWJVjI2AfL8SnMNwTywHJBnBOYl5ZSmAvnO+Tn5RVBpfWN9Q0tLC6CoS2JJqlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, Column = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Column]), "Column", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Num1", Int64.Type}, {"Num2", type number}, {"Date", type date}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "List", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"List.1", "List.2", "List.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"List.1", Int64.Type}, {"List.2", Int64.Type}, {"List.3", Int64.Type}})
in
    #"Changed Type1"

The challenge here is you have to pick the max number of columns for the split. Easy if it's known, otherwise you might need to calculate the max number of "|" characters in the column. Result of split by columns

Upvotes: 1

Related Questions