Deleting Blank Columns in Power Query

khawarameer

Board Regular
Joined
Jun 2, 2009
Messages
148
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Pivotor

New Member
Joined
Jan 27, 2016
Messages
2
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
 

tzenekkik

New Member
Joined
Jun 3, 2002
Messages
39
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?
 

Forum statistics

Threads
1,089,453
Messages
5,408,317
Members
403,196
Latest member
annph

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top