Deleting Blank Columns in Power Query

[khawarameer]

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

 

[khawarameer]

Dear MVPs, any one please respond.

 

[Pivotor]

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.

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

 

[tzenekkik]

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?

 

[sandy666]

I missed what is your basic question, so here is how to remove blank columns