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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.