Dynamically remove columns that contain a specific word in the header - Power Query

It is very common when importing data from excel or pdfs that you get a lot of empty columns. In today's video I will show you how to remove those columns dynamically using power query.

Chapters:
00:00 Intro & case description
00:50 List all the column names in your table
01:11 Find the column names using List.FindText
01:50 Remove all columns that contains previous list with Table.RemoveColumns
02:30 Test the solution
Done!


SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:


Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲


************





************


QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#CURBAL #SUBSCRIBE

[AllysonHamilton]

This video just helped me save a ridiculous amount of time I'd been spending editing queries when the sheet provider keeps changing their sheet structure. Much appreciated!

[dmb]

Fantastically helpful, great functionality explained well.

[WonkieDonk]

Great video. Perfectly explained. Thank you!

[RobertoStaltari]

I started to use List functions to manipulate tables with hundreds of columns most of them empty. It takes some little coding but save hundreds of clicks. Thanks Ruth!

[nehasingh]

Wow this is great. I wish Power BI had this option in GUI itself to remove columns based on some condition. Thanks a ton!

[christinalee]

Thank you for updating this great method, super easy when I have to remove hundreds of columns

[AlvaroPerez-oxul]

Thank you woman. I've been looking for this.

[liewwengfoo]

Hi, Ruth, what if i wanna do it the opposite way making it more dynamic, using not equal to on the function to exclude these column names, ive tried to use <> and not, but both have shown me syntax error, what should i do? thanks

[paspuggie]

Brilliant Ruth. Wish I knew this a long time ago LOL

[vida]

Great example Ruth. It inspired me to create a query for my situation. Our weekly file providers sometimes add extra columns with extra information and I wouldn’t know how they would name these extra columns. So, after converting Source to List, I had to covert it back to Table and select the required columns, then again convert to List and finally keep columns from this List:


let

Source = “add your source”

Custom1 = Table.ColumnNames(Source),

#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Column1] = "Customer" or [Column1] = "Supplier")),

Custom2 = Table.ToList(#"Filtered Rows"),

Custom3 = Table.SelectColumns(Source, Custom2)

in

Custom3

[austinleedavisTeaches]

I wish I could Thumbs Up this more than once. Thank you!

[selenevera]

Thank you Ruth for this incredible video that helped me a lot ; ). I was wondering if it's possible to search for many words at once. e.g. List.FindText(custom1, {{"Column"}, {"File"}})

[ingridye]

Thanks for the sharing! what if wanting to remove columns name contains "A" or "B" which means more than one texts to be found - List.FindText only works for finding one text.
Thank you very much!

[nachomiranda]

Thanks a lot.
You just saved my life 🙂

[juansepowerplatform]

Great video, I usually just selected the columns that I always wanted to keep and then remove other columns to get around this. Good to know the M solution as well. Thank you 😁😀

[A_Ejem]

Very useful video, I was looking for this solution today, you saved me :)

[zbatevp-vlogs]

Amazing Ruth! Very useful for beginners!

[nikolailic]

Great video Ruth, as always! The question is really tricky: if it relates to creating custom visualization, I would say node.js.

[nickdoy]

It uses JavasScript so JQuery. Keep up the good work Ruth 👏

[serigamel]

How can we do this to keep certain columns instead of removing them?