Split column and Combine Columns

Problem Statement:

How to perform a ‘split and merge’ on columns to change (a) into (b)?

Solution:

In order to perform a transformation like the one shown above, the approach is to first split the data into columns and rows, then merge them back to get the desired result.

Here’s how it can be done:

1. Beginning with (a), we’ll first have to split the column by delimiter (using space as the delimiter) into columns, at the end of which we should get something like this:

Note that after performing the operation, the data in the second column will look like 125, 247, and 689 instead of (1,2,5), (2,4,7), and (6,8,9). This is because the data type is automatically changed to a whole number from text. The expected values can be brought by deleting the ‘changed type’ step.

2. Now we have to split the comma separated numbers into different rows which can be done by using the split column by delimiter tool. Only this time we chose comma as our delimiter and ‘Split into rows’ by selecting the Advanced options. The result should look like this:

3. The final result can be achieved by performing a simple merge on the two columns to get this:

The Code

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45W8kvMTVUISszLVorViVZySi0uUTDUMdIxAXP9MpNTFUx0THXMwFz3/PwUoCxQLhYA”, 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}}),
#”Promoted Headers” = Table.PromoteHeaders(#”Changed Type”, [PromoteAllScalars=true]),
#”Changed Type1" = Table.TransformColumnTypes(#”Promoted Headers”,{{“Name Rank”, type text}}),
#”Split Column by Delimiter” = Table.SplitColumn(#”Changed Type1", “Name Rank”, Splitter.SplitTextByDelimiter(“ “, QuoteStyle.Csv), {“Name Rank.1”, “Name Rank.2”}),
#”Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#”Split Column by Delimiter”, {{“Name Rank.2”, Splitter.SplitTextByDelimiter(“,”, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), “Name Rank.2”),
#”Changed Type2" = Table.TransformColumnTypes(#”Split Column by Delimiter1",{{“Name Rank.1”, type text}, {“Name Rank.2”, Int64.Type}}),
#”Merged Columns” = Table.CombineColumns(Table.TransformColumnTypes(#”Changed Type2", {{“Name Rank.2”, type text}}, “en-US”),{“Name Rank.1”, “Name Rank.2”},Combiner.CombineTextByDelimiter(“ “, QuoteStyle.None),”Merged”)
in
#”Merged Columns”

You can get file here

You can get video here

Power Bi Split column and Combine Columns

Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.

You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share