Power BI - Split one cell into multiple columns

[Rudy123]

Dear All, 

How can I transform the variable BA in the left table to the wide format in the right one using Power BI?

Many thanks in advance. 

Rudy

[Rudy123]

Thanks lkalawski! 

Your solution works fine and I was surprised of how powerful and handy Power BI is for this kind of issues. 

[Rudy123]

Dear All, 

I would have a question related to this situation.

How can I create a pie chart for the frequency of each basket category on the total ( 5 items) with a slider to select across the categories (ANIMAL, FISH, VEGETAGLE)?

My initial situation is that on the left table for which I create the split to multiple columns. Is there a way to insert a filter/slider in a visual for "Contain the text FISH" so that one can avoid the transformation step?

Many thanks in advance!

Rudy

[lkalawski · Accepted solution]

Hi @Rudy123 ,

To achieve this, use Power Query and follow these steps:

1. Split Column BA by Delimiter to rows (not columns!)

2. Add Custom Column: Custom = 1.

3. Pivot columns: BA and Custom. 

4. IN the Pivoted Column option change the Aggreate Value Function to Count(ALL) to receive zeros.

The result:

Memorable Member | Former Super User If I helped, please accept the solution and give kudos!  Linkedin