How to split the the Column into Multiple rows.

[kapil512]

Hi,

Can you please helpw me out in this, i am trying snce two days.

I want to split the column data into different rows with same data.

Example: Column1|Comun2|Comun3|Column4|

Data: 1|kapil;dev;raj|4|5

Data:2|dev;ram;john;peter|3|5

Final out put:

1|kapil|4|5

1|dev|4|5

1|raj|4|5

2|dev|3|5

2|ram|3|5

2|john|3|5

2|peter|3|5

I have tried the below Logic, and when my expand the table i am seeing lot od duplicate data.

Table.AddColumn(#"Replaced Value","Custom",each Table.FromColumns({Text.Split([Answer Choices],";")}))

Can anyone please help me out?

Thanks,

Kapil

[TomMartens · Accepted solution]

Hey,

in the query editor, I just marked the column and from the context menu of the marked column I chose "Split Coliumn" -> "By Delimiter"

From the dialog I chose Advanced Options as shown below

This leads to the following result

Hope this is what you are looking for

Regards

Tom

[kskarthickpbi]

Hi,

May I know how to do this reverse, like below

Table

---------------------------------------

Col1  Col2   Col3  

 1      2019   ab

 2      2019   xy

 3      2018   zd

 4      2017   rt

 5      2019   rt

 6      2017   cv

O/P should like

--------------------------------------------

Col1  Col2   Col3  

 1      2019   ab,xy,rt

 2      2019   ab,xy,rt

 3      2018   zd

 4      2017   rt,cv

 5      2019   ab,xy,rt

 6      2017   rt,cv

[kapil512]

Thank you So much Tom, its really works like champ..!

And i have one more senario, once i split the data into rows, need to calucate the percentage of the column.

Let me give you onc example:

A	B	C	D
TR	Barriers	Risk	Risk
TR	Barriers	Other	Risk
TR	Barriers	Both Development	Risk
TR	Barriers	Cataract Development	Risk
TR	Barriers	Risk	Both Development
TR	Barriers	Other	Both Development
TR	Barriers	Both Development	Both Development
TR	Barriers	Cataract Development	Both Development

In given example A and B is the filters, based those filter i want create a Stacked column chart on X axis for "C" values and for "Y" access i need to calculate of the %. if it is "Risk" that could be 4/8 and if it is "Both Development". 4/8. on "Y" Axis.

if this not clear let me know, so that i will share by pbix file to you.

Thank you mush for help.

Thanks,

Kapil

[TomMartens]

Hey,

I guess it would be really helpful if you could share a pbix, please start a thread, you may call is "Stacked column chart".

Regards

Tom

[kapil512]

Thank you Tom.

I will do that.