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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
DaGemsta
Helper I
Helper I

Consolidate columns removing blank spaces and then remove empty columns in M query

Hi guys

 

I have the following table

 

ClientCodeInvoice Email 1Invoice Email 2Invoice Email 3Invoice Email 4Invoice Email 5Invoice Email 6
comp1userabc@comp1.comuserdea@comp1.com user31@comp1.com user14@comp1.com
comp2 user4@comp2.com  user1@comp2.com 
comp3      
comp4  user11@comp4.com  user2@comp4.com
comp5   userbob@comp5.com  
comp6userval@comp6.com    user26@comp6.com
comp7 user19@comp7.com    
comp8      
comp9     

usertom@comp9.com

 

And I want to use M Query to reduce the number of columns I've got without losing any email addresses.

 

Desired result should look like the below

 

ClientCodeInvoice Email 1Invoice Email 2Invoice Email 3Invoice Email 4
comp1userabc@comp1.comuserdea@comp1.comuser31@comp1.comuser14@comp1.com
comp2user4@comp2.comuser1@comp2.com  
comp3    
comp4user11@comp4.comuser2@comp4.com  
comp5userbob@comp5.com   
comp6userval@comp6.comuser26@comp6.com  
comp7user19@comp7.com   
comp8    
comp9usertom@comp9.com   

 

I've tried using conditional columns but can't wrap my brain around it.

 

Anyone know how to do this please?

1 ACCEPTED SOLUTION
serpiva64
Solution Sage
Solution Sage

Hi, 

You can obtain this applying the steps indicated in the attached file

If this post is useful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

 

View solution in original post

2 REPLIES 2
DaGemsta
Helper I
Helper I

Thanks @serpiva64  Smashed it! 😁

serpiva64
Solution Sage
Solution Sage

Hi, 

You can obtain this applying the steps indicated in the attached file

If this post is useful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.