Denormalisation of multiple values per cell with counts against each in Power BI

[DataMarsh]

I have a bit of a strange one, where I have more than one value against one count, i.e. the count is the same for both values, so the values have been merged into one cell, is there any way to split out so that I can use it in a visualisation in Power BI?

For example in the image below, on row 3 both Apple and Pear have the value 3, but I need to have these in a separate row like the image on the right to get it into a visualisation that makes sense, i.e. 3 against Apple and also 3 against Pear. Any ideas, the source is a Google/Excel spreadsheet, I am hoping that this can be done in Power BI rather than the source (which I am unable to edit)?

[Daniel29195 · Accepted solution]

@DataMarsh 

this is doable with power query .

select the column you want to split, then go  under transform --> split by columns ,  and in the configuration, choose to split by row .  ( dont forget to put the delimiter base on which you want to split the data on ) 

If this helps you with your question, please mark it as the solution ✅ so can you can help other people in the community to find it easily .