Dynamically Remove Empty Columns in Power Query

Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
- - - - My Courses - - - -
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
- - - - Blog - - - -
www.goodly.co.in/

[@GoodlyChandeep]

Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/

[@juansepowerplatform]

Thanks for the video, sure there are many other ways to get this done using the UI, but your video showcases how to really use M, how to refer back to previous steps, how to break your steps into logical chucks. Best channel to really learn M language. Thank you sir

[@davidbakker1460]

Awesome video as always, thank you! I’m actually exploring writing the M code myself, instead of using the UI. It is actually working out quite wel, thanks to your videos. Awesome that we can combine multiple steps in 1 step when we write the M code ourselves.

[@CeliaAlvesSolveExcel]

Great tutorial! Thank you!

[@suzannekatz3603]

Just what the doctor ordered. Thanks!

[@FurquimRafa]

Man, your videos are amazing and very didactic! Keep the good work!

[@stephanweaver1960]

Good I did the same but using a configuration file that lists expected column names and use that to clean my input file. Didactic suggestion that would make us at the beginning of your videos also state the lessons to be learned, and then just recap at the end. Excellent

[@mirrrvelll5164]

Useful video. It happens quite often actually that I need to remove columns manually.

[@isidrebague3659]

Just Great, as usual!! Thank You.

[@realpulsecoin]

This is fire Goodly - great!

[@DK_85]

Thanks a lot Chandeep! Very helpful!

[@GoodlyChandeep]

Happy to help!

[@engahmedelbendary1566]

thx alot for your effort Really i had learned more Information from your Videos

[@brajsharma]

Good solution buddy 👍

[@msmc1995]

Thank you very much

[@askulasaatwik8thc644]

Thank you!, Learned a new thing today in Power Query.

[@GoodlyChandeep]

Glad it was helpful!

[@Chillman666]

Another very cool lesson

[@txreal2]

Nice! 🙋‍♂For data modeling, how do I keep only ColNames that have 80% or more of non-nulls?

[@1989PU]

Great Video. What i usually use ( from GUI itself) is something like this: 1. Transpose (before promoting headers) 2. Remove Rows > Remove Blank Rows 3. Transpose back again.

[@odallamico]

With large amounts of data, your process can become slow.

[@MrKamranhaider0]

Amazing

[@wayneedmondson1065]

Hi Chandeep. Excellent lesson! Another approach is from the reverse.. using Table.SelectColumns to specifically select only the desired columns, thereby filtering out everything else, as in: = Table.SelectColumns(Table.PromoteHeaders(Table.Combine(Source[Data])),{"Name","Age","Company"}). Of course, if you add more desired columns, you would have to modify the query for the additions. I guess it depends on whether you are more prone to adding new columns of desired data that need to be included or adding new columns of undesired data that need to be excluded. Either way, good to know both approaches. Thanks for sharing this! Always good learning at Goodly :)) Thumbs up!!

[@raimundojs9547]

Great video! Suggestions about the following function, please: (Tab as table) as table => let LstOriginalColumnNames = Table.ColumnNames(Tab), LstColumnListOfLists = Table.ToColumns(Tab), TbConvertedToTable = Table.FromValue(LstColumnListOfLists, [DefaultColumnName = "ListOfLists"]), TbIndexColumn = Table.AddIndexColumn(TbConvertedToTable, "Index", 0, 1, Int64.Type), TbNonNullColsIndex = Table.SelectRows(TbIndexColumn, each List.NonNullCount([ListOfLists]) 0), LstNonNullCols = Table.AddColumn(TbNonNullColsIndex, "NonNullCols", each LstOriginalColumnNames{[Index]})[NonNullCols], TbOutput = Table.SelectColumns(Tab, LstNonNullCols) in TbOutput

[@rlds07]

Excellent tip. How to do when we do not yet have the header set?

[@juja2819]

Thank you! :) But what if there is column(s) with other names then column and they still has only null in each row?

[@faridPQ]

Will you please make a video on finding Subtotal in Power Query like Subtotal in Excel pivot but not Group by?

[@txreal2]

Can I import a selected columns list that I have in Excel?

[@rahulshewale9206]

Hi sir , Thanks you so much for 📷 Could you please make video how to fetch table from NSE INDIA website ?

[@muhawatali1224]

Sir Great Help. Please tell me how i can show the Formulas or functions while Typing some part as in your video. I am using office 19.

[@svendewinter7441]

Hi Goodly - Why do you not transpose & remove blank rows & transpose again? Seems easier for blank column removal and also dynamic

[@Alan.DL7]

That will only work for blank columns but if you have some text then is not possible I believe.

[@vinod71234]

Apne kaha this is simple data.....please make video on how to handle complex data in dax

[@holahandstrom]

Why not just Transpose, Unfilter Blank, and Transpose again. Done.

[@Rood6]

This was really helpful, however for some reason it seems to merge all data into a single table when there are multiple sheets in the excelworkbook. Is there a way to refer to a certain Item?

[@GoodlyChandeep]

You'll have the option to navigate to a single workbook in the Navigation step (typically after the source step)

[@RobertoStaltari]

I like that you don't use the user interface. It's the only way to learn M.

[@BoxerDogs]

The formulas you are typing in, is that the DAX language or something else?

[@thebhaskarjoshi]

M Language

[@lionels839]

How to dynamically remove blanks rows?

[@jacobmurray6731]

I'd be keen to know how to delete columns where all the rows are empty but there is a normal header

[@GoodlyChandeep]

Noted:) Need to put together a video for that.

[@dharmmu]

Sorry but the solution is not correct. You are removing not the empty columns but the columns which don't have any header name. These two are different issue. Sometime our column may have data, might be 10th or 20th row, in that case your formula will ignore that column, which is not correct. In my scenario by removing the whole column, you are removing some data which was not type properly. There should be some other option, which remove the column only if all the row in that column is nulll.