Concatenating values as a tabular format in Power BI

Introduction: What is concatenating?

The Power BI CONCATENATE function is a DAX function that combines two text strings into one. Text, integers, or Boolean values displayed as text, or a mix of those elements can be connected. If the column has appropriate values, you may also utilize a column reference.

Concatenating in columns means that you can concatenate strings where column names are given as arguments and the concatenation is applied on a row-by-row basis for the columns given as arguments.

Typical approach for concatenating multiple columns

The below formula returns the Concatenate Columns in the financials table. The concatenation applied on the “Segment”, “Country” and “Discount Band” columns. This is a typical method for concatenating multiple strings when you have more than two values to use as arguments.

Concatenate Columns = CONCATENATE(financials[Segment],

CONCATENATE(“ “,

CONCATENATE(financials[Country],

CONCATENATE(“ “,financials[Discount Band]))

))

Output

Main drawbacks of this method

The main drawback of this method is you can’t get the properly align view in a tabular structure for the concatenate columns as below.

How you can achieve the tabular structure for concatenate columns

In this case, we’re going to discuss how we can vertically align the concatenate columns as shown in the image above.

1. Initially you need to set the length of each cell string values to the same length for each column attribute. Follow below steps to proceed.

i. Count the text length of the cell values in each column. (Refer code 1 or 2 in below power query editor)

ii. Set the number which would decide the length of the string value for all cells in each column. The number should be more than the maximum actual length of the string value. So, the number may differ for each column. Let’s say you decide 25 as the length of the string value for “Segment” column.

iii. The actual length of the string value for the cell value “Government” is 10 as you already calculated. Then you need to fill the remaining 15 by any symbols other than the “Space”. The “Space” could be trimmed and go off while you transform & load in power bi as a default. So, it’s better to avoid “Space” as a symbol in this scenario. Here we used repeating “Underscore” to fill the remaining. (Refer code 3 or 4 in below)

iv. Now, you can use “Repeate_Segment” column to concatenate along with “Segment” column to show whole “Segment” column as a constant string length (25).

You can follow the Power query code to edit the above logic. The codes which blurred are the already existing codes which we don’t need to touch.

Input in Power Query Editor

2. Once you’ve done with power query editor. You can extract the concatenate column in table visual in report view. In visual you need to set all the value Font to “Consolas” which is mostly used in programming environments and other circumstances where a monospaced font is specified. All characters have the same width, like old typewriters.

Final Report view