Forum Discussion
How to split columns with values which have commas
Hi,
I have a column which has mulitple values and they are seperated by commas, however, the values themselves have commas as part of the value. This challenges me to split them into columns easily using comma seperator. Any ideas how I can tackle it? I'm quite new to the Power BI. Below is the sample data set.
Column Name
| Online Shopping Payment, Utility Payment (Electricity, Water bill, YCDC etc.,), Donation Payment (Foundation, Charity and etc.) |
| Internet Bill Payment, IT and Mobile Payment (IT and mobile devices Payment and etc.), Online Shopping Payment, Travel Payment (Tour payment), Utility Payment (Electricity, Water bill, YCDC etc.,), Ticketing Payment (Bus Ticket, Air Ticket and etc.), Donation Payment (Foundation, Charity and etc.), Fortune Telling Payment |
| Internet Bill Payment, Online Shopping Payment, Fortune Telling Payment |
| Internet Bill Payment, Online Shopping Payment |
| Internet Bill Payment, Donation Payment (Foundation, Charity and etc.) |
| Insurance Payment, Internet Bill Payment, Entertainment Payment (Skynet, cannal+ ,ect.,), Utility Payment (Electricity, Water bill, YCDC etc.,), Assorted Bill Payment (1-2-3 Service and etc.) |
| Insurance Payment, Internet Bill Payment, IT and Mobile Payment (IT and mobile devices Payment and etc.), Trading Payment (B2B payement), Entertainment Payment (Skynet, cannal+ ,ect.,), Utility Payment (Electricity, Water bill, YCDC etc.,), Donation Payment (Foundation, Charity and etc.) |
| Internet Bill Payment |
| Internet Bill Payment, Online Shopping Payment, Transportation Payment (Grab Payment, Delivery Service Payment and etc.) |
| Internet Bill Payment, Online Shopping Payment, Donation Payment (Foundation, Charity and etc.) |
| Insurance Payment, Internet Bill Payment, IT and Mobile Payment (IT and mobile devices Payment and etc.), Online Shopping Payment, Travel Payment (Tour payment), Entertainment Payment (Skynet, cannal+ ,ect.,), Utility Payment (Electricity, Water bill, YCDC etc.,), Ticketing Payment (Bus Ticket, Air Ticket and etc.), Event Ticket (Sport Ticket, Festival Ticket, and etc.), Donation Payment (Foundation, Charity and etc.), Transportation Payment (Grab Payment, Delivery Service Payment and etc.) |
| Education Payment (Tution Fees and etc.), Internet Bill Payment, IT and Mobile Payment (IT and mobile devices Payment and etc.), Online Shopping Payment, Travel Payment (Tour payment), Trading Payment (B2B payement), Entertainment Payment (Skynet, cannal+ ,ect.,), Utility Payment (Electricity, Water bill, YCDC etc.,), Games Payment, Ticketing Payment (Bus Ticket, Air Ticket and etc.), Event Ticket (Sport Ticket, Festival Ticket, and etc.), Donation Payment (Foundation, Charity and etc.), Loan Repayment (Bank Loans, Credit Cards Bills etc), Transportation Payment (Grab Payment, Delivery Service Payment and etc.), Assorted Bill Payment (1-2-3 Service and etc.) |
Anonymous , Based on what Got, first replace ), or ,) with something else and then you can split by comma in power query
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
Split Column Power Query: https://youtu.be/FyO9Vmhcfag
2 Replies
Anonymous , Based on what Got, first replace ), or ,) with something else and then you can split by comma in power query
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
Split Column Power Query: https://youtu.be/FyO9Vmhcfag
Hi amitchandak thanks for the suggestion on the quetion. I have a follow up questions on data display.
What I did now is that I have splitted values into seperate columns, unpivot them and throw them into visuals, however, the total count of values is now incorrect due to the replicated rows. Any suggestions on how should I handle it?