Forum Discussion
How can PBI accept multiple responses from excel document's cell that uses | to delimit?
What I am trying to figure out is how can PBI ingest this (data snippet below) to where I can delimit these reponses based on |. Basically taking this data, and PBI being able to graph "rice has 2 people that list it as there favorite food, Steak has 1, etc...." The first soltuion I came across was first to split the columns using | , and then unpivoted the columns and that does work. Howeever this solution is will create a ton of data. Is there any other function or solution I can use? Thanks so much for the help!
Hi Anonymous
Here is another method.
When you split the column by delimiter "|", instead of splitting it into columns by default, you can expand Advanced options and select Rows. You will have the following table.
Then you can select Favorite Foods column, select Group By in Transform ribbon and perform Count Rows operation on it. You will get the count number per Food item. This method will not create a lot of data.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
Split and unpivot is probably the best way to do this. You could use the SEARCH function, leaving the column delimited, but that would likely be slower and you would still need to generate a table with the distinct list of foods to be used in a slicer/visual (to get the selectedvalue to be used in the SEARCH).
Pat
Hi Anonymous
Here is another method.
When you split the column by delimiter "|", instead of splitting it into columns by default, you can expand Advanced options and select Rows. You will have the following table.
Then you can select Favorite Foods column, select Group By in Transform ribbon and perform Count Rows operation on it. You will get the count number per Food item. This method will not create a lot of data.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
