Deleting Blank Columns in Power Query

khawarameer

Board Regular
Joined
Jun 2, 2009
Messages
152
Hello All,

I need to import a 256 column table using power query [every fortnight through ODBC]. There are at least 150 columns in the table are empty however there is no fixed criteria that which column would be empty. I order to refine the data i need to delete these empty columns.

I used the Transpose option in transform menu to convert columns in to rows and used the delete blank rows option however it did not work as the column headers were appearing the every row after flip.

Now i need a formula which will count the number of items in each row and if that number is equal to 1 then that row is filtered. In excel i can do that using counta function but i am unable to do that in power query. Can any one help in this regard.

Khawar A. Malik
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hello,

I had a similar issue and came out with this solution. I just post it here in case it could help you or someone else. It is probably not the most optimised solution but it works.

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Reorganized = Table.FromColumns({Table.ColumnNames(Source),Table.ToColumns(Source)}),
    #"Added Custom" = Table.AddColumn(Reorganized, "IsNull", each if List.NonNullCount(List.Distinct([Column2]))=0 then "Yes" else "No"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([IsNull] = "No")),
    #"Clean Table" = Table.SelectColumns(Source,Table.Column(#"Filtered Rows","Column1"))
in
    #"Clean Table"

You just have to replace the source with the table on which you want to apply the operation
 
Upvote 0
Pivotor,

This is actually a great solution. I would not know why you say "It is probably not the most optimised solution but it works."
It works great!
Your solution should become more known to people working with Power Pivot.

I have seen solutions with UnPivoting columns and they too work very well, but I cannot imaging that they are faster/more optimised.
https://www.youtube.com/watch?v=-owZ7G880Jc
Or do I miss something? Anybody?
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top