Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
newgirl
Helper V
Helper V

Exclue Blank Row in SUMMARIZECOLUMNS

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.

summarize1.jpg

 

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?

summarize2.jpg

1 ACCEPTED SOLUTION
Thejeswar
Resident Rockstar
Resident Rockstar

Hi @newgirl ,

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

 

Thejeswar_0-1629441678779.png

 

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

Thejeswar_1-1629441846438.png

 

 

View solution in original post

4 REPLIES 4
Thejeswar
Resident Rockstar
Resident Rockstar

Hi @newgirl ,

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

 

Thejeswar_0-1629441678779.png

 

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

Thejeswar_1-1629441846438.png

 

 

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?

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
Not applicable

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.