Forum Discussion
How to split the the Column into Multiple rows.
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
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
5 Replies
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
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
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
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
