cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors