Format Table Data to Show Horizontally in Power BI

By | March 29, 2019

Introduction

We recently had business requirement to show account details (account number, Email) horizontally by “Account Name” in Power BI Report. When we selected table to display data in our Power BI report, by default it display data vertically, as you can see in our below screenshot:

Format Table Data to Show Horizontally in Power BI

We explored more and figure out that we can’t format table to show values horizontally. So for workaround by using matrix we can format table to display data horizontally.

Please follow below steps to show data horizontally in table:

1. At first we need to select Matrix to display data instead of table, as you can see in below screenshot:

Format Table Data to Show Horizontally in Power BI

2. Now you need to select fields that you want to show in table, here in our case we are showing Account name (name) as a Column, Account Number (account number) and Email (emailaddress1) as a Values. Please check in our below screenshot:

When we added a text field to the ‘values’ area, the fieldname was displayed as ‘First FieldName’ rather than just ‘FieldName’. In our case, it shows field name as “First account number” and “First emailaddress1”. So we have renamed both field name.

FYI, by default selected property for text field is “First”.

 

Why does it show “First Field Name” rather than “Field Name“?

We used the matrix so it groups the result based on “Column” value. In our example the column name is “Name” (i.e. Account Name) so it groups the result by “Account Name” value.

We added Account Number under values and selected property as “First” (i.e. First Account Number). Because of this grouping on Account Name it shows only first Account number where multiple Accounts have same name. For better understanding please refer the below example:

 Table: Sample data

format table

Using Matrix:

  • When we selected First option for “Account Number” column i.e. First Account Number

format table

  • When we selected Last option for “Account Number” column i.e. Last Account Number

format table

  • When we selected Count option property for Account Number i.e. Count of Account Number

format table

So, according to your requirement you need to select property like First, Last, Count, Count (Distinct) and if you want rename the field column name.

3. In final steps you need to turn on “Shows on rows” property under Values in Format tab. Please follow our below screenshots:

Power BI

4. Now as you can see in our below screenshot our table data Account Number and Email are formatted horizontally and showing by Account Name.

Conclusions

By using Matrix feature of Power BI, we can aligned values in table horizontally.

2 thoughts on “Format Table Data to Show Horizontally in Power BI

  1. Ian Wainwright

    Hi
    Loved the article. Just what I was looking for. One question, though – when I added a text field to the ‘values’ area, the fieldname showed as ‘First FieldName’ rather than just ‘FieldName’ as in your example above. I had to edit the field name in order to remove ‘First ‘. Did you have the same issue or have I missed something?
    Regards
    Ian

    Reply
    1. inogic Post author

      Yes, you need to edit the field name. In our case, we have renamed “First Account Number” with “Account Number”.

      FYI, here we have used matrix so it groups the result based on “Column” value. In our example, the column name is “Name”(i.e. Account Name) so it groups the result by “Account Name” value.

      We added Account Number under values and select property as “First” (i.e. First Account Number) then because of grouping on Account Name it shows only first Account number even if multiple Accounts have same name. For better understanding please refer our below example:

      Table: Sample data

      Power BI

      Using Matrix:

      1. When we selected First option for “Account Number” column i.e. First Account Number

      Power BI

      2. When we selected Last option for “Account Number” column i.e. Last Account Number

      Power BI

      3. When we selected Count option property for Account Number i.e. Count of Account Number

      Power BI

      So, here according to your requirement you need to select property like First, Last, Count, Count(Distinct).

      Hope this helps.

      Thanks!

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *