Search code examples
powerbidaxm

Concatenate text with newlines in PowerBI

If I have a column in a table where each cell contains text, how can I push them as output into e.g. a card and separate the cells with a new line?

I have been using the CONCATENATEX function, which takes a delimiter argument; however the standard new line character ('\n') doesn't work.

Solution

  • It is possible to pass Unicode characters to the CONCATENATEX function, using the UNICHAR(number) function.

    The number parameter corresponds what looks to be the decimal UTF-16 or UTF-32 encodings (as shown here).

    This means a new line is given by UNICHAR(10).

    A final solution might then be: CONCATENATEX(TableName, TableName[TextColumn], UNICHAR(10))

    Here is a screenshot that shows:

    • the input table in Excel (top left)
    • The table once imported into Power BI Desktop (top right)
    • The Measure 'Description' and the output within a Card object (bottom)

    enter image description here

    In the last line of the Measure code, marked yellow, you can see the use of UNICHAR(10) as a new line separator.

    If nothing were to be selected in the Slicer object (i.e. everything is selected by default - no filter is used), then "Show other text" would be displayed in the Card.