Exclue Blank Row in SUMMARIZECOLUMNS

[newgirl]

Hi, everyone!

In my file I have a table that lists down the jobsites created in the system and for each jobsite, there's an assigned Vice President, Manager, and Salesperson.

I needed a separate table that lists down the VP-Manager-Salesperson combination without duplicate, so I used the SUMMARIZECOLUMNS measure. This worked perfectly but at the bottom of the table, there's a blank row. I checked each column of the VP, Manager, and Salesperson columns from the original table, and there's really no blank value.

How can I remove this blank row in this calculated table?

[Thejeswar · Accepted solution]

Hi @newgirl ,

You can remove the blanks rows using the option present in the Power Query Editor. Shown below the same

Clicking on Remove Blank Rows will add a new step as "Removed Blank Rows" in the applied steps

Personally, a better way to create this table rather than using SUMMARIZECOLUMNS DAX is to just duplicate the table, remove the unwanted columns and remove duplicate rows

The Below screenshot shows the location where you can get these options

[newgirl]

Hi @Thejeswar ! I tried it in Query Editor instead, thanks for the idea. Can I just ask which option makes the PBI loading process "more efficient"? Is it creating via calculated table or creating it in query editor?

[Thejeswar]

Hi  @newgirl ,

As per my understanding, the better approach would be to have it in Query editor as creating Calculated tables would increase your model storage size as well as your refresh times thereby resulting in decreased performance as the calculated table responds for any changes in the underying data.

[Anonymous]

You should ALWAYS create/cleanse/transform data in Power Query. DAX is a language for data analysis. M is a language for data mash-up. By the way, SUMMARIZECOLUMNS is not a measure. It's a function.