Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Showing a list without separators

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

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • RichardJ's avatar
    RichardJ
    6 years ago

    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

4 Replies

  • RichardJ's avatar
    RichardJ
    Responsive Resident
    6 years ago

    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