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

[DaGemsta]

Hi guys

I have the following table

ClientCode	Invoice Email 1	Invoice Email 2	Invoice Email 3	Invoice Email 4	Invoice Email 5	Invoice Email 6
comp1	userabc@comp1.com	userdea@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
comp6	userval@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

ClientCode	Invoice Email 1	Invoice Email 2	Invoice Email 3	Invoice Email 4
comp1	userabc@comp1.com	userdea@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
comp6	userval@comp6.com	user26@comp6.com
comp7	user19@comp7.com
comp8
comp9	usertom@comp9.com

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

Anyone know how to do this please?

[DaGemsta]

Thanks @serpiva64  Smashed it! 😁

[serpiva64 · Accepted solution]

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 !