Showing a list without separators

[Anonymous]

Hi,

We have a field with a list that we want to show in Power BI.

Example field:

     A       dimension1;dimension2

     B       dimensionA;dimensionB;dimensionC

Result should be something like:

     A       dimension1

               dimension2

     B       dimensionA

              dimensionB

              dimensionC

Can somebody help me out with that?

Our can help me with another solution.

Thanks,

Davina

[amitchandak]

@Anonymous , In Data Transformation/Edit Query mode, You can split a column by Delimiter, Use Split into rows 

Refer for steps

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[RichardJ · Accepted solution]

Hi Davina,

Theres a couple of options.

Option 1

If you go to the query editor and select Split Column by Delimiter as shown:

Split Column

Choose to split by the Semi Colon if it isnt already selected

Split Column

Select the Category Column as shown, then choose Unpivot Other Columns.

Unpivot

The 'Category' and 'Value' columns show the information you requested.

Review

Option 2

Alternatively, another option is to use the 'Replace Values' command.

Select the Values Column, then choose 'Replace Values' to replace the semicolon with a Carriage Return and Line Feed.

Replace delimiter with Carraige Return and Line Feed

This will provide the data as shown if this is preferable.

Redults

Hopefully this answers your question @Anonymous - Its the first one i've answered on this forum so would appreciate if you could let me know.

Cheers,

Richard

[AmberJane]

This also resolved my issue in this post: https://community.powerbi.com/t5/Desktop/Separate-products-that-are-listed-in-data-with-or/m-p/23621... Thank you!! 

[Anonymous]

@RichardJThanks for your answer. Option 2 was what I needed.