ChandakAmit
Published in

ChandakAmit

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

--

--

--

Business Intelligence, Analytics, ML , Data Science, BI tools and technologies, Power BI, Tableau, Incorta, Qilk Sense, Quicksight, Python, R, Spark, Kafka, Solr, Elastic Search

Recommended from Medium

What is relational?

Golang For Mobile Development

Use cases of data and Schemaspy: Database Management

The Ultimate Guide To Preparing Coding Interview in 2020— Part 1: Basic Fundamentals

How to improve CI/CD efficiency in your development process

Stop Spawn Routines Upon Player Death in Unity3D

Cost saving on AWS — part 2

Auto Deploy Your Client and Server From Separate Repos Under Single Heroko App

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Amit Chandak

Amit Chandak

Amit has 18+ years of experience in Business Intelligence, Analytics, Data Science. He is Chief Analytics Officer at Kanerika & Power BI community Super User

More from Medium

How to implement Row Level Security in Power BI (Part I)

What is Performance Analyzer in PowerBI?

Customer Sales Analytics with Power BI

Power BI Dataflow JSON Viewer