Forum Discussion

one_eon's avatar
one_eon
Frequent Visitor
4 years ago
Solved

Formula to Automatically Remove Blank Columns from a table

I have a folder of workbooks I am trying to import.  The data is messy.  For each of the workbooks I want to remove any blank columns before I combine the data.

So my data looks like this:

DataFile Name
TableFile1.xlsx
TableFile2.xlsx
TableFile3.xlsx

 

I tried to add a calculated column with the formula:

 

Table.SelectColumns(
  [Data], 
  List.Select(Table.ColumnNames([Data]), each not List.IsEmpty(Table.Column([Data], _)))
)

 

 

The error i get is:
Expression.Error: We cannot apply field access to the type Text.

 

I appreciate any assistance.

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    This is a context reference issue. The problem here is that M thinks you want to take the [Data] column from the column name. You can clear this up by using a new name for your selection function.

    Table.SelectColumns(
  [Data], 
  List.Select(
      Table.ColumnNames([Data]),
      (colName) => List.NonNullCount(Table.Column([Data], colName)) > 0
  )
)

    Note: List.IsEmpty returns FALSE for a column of null values since, e.g., {null, null} is not the same as {}.

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    This is a context reference issue. The problem here is that M thinks you want to take the [Data] column from the column name. You can clear this up by using a new name for your selection function.

    Table.SelectColumns(
  [Data], 
  List.Select(
      Table.ColumnNames([Data]),
      (colName) => List.NonNullCount(Table.Column([Data], colName)) > 0
  )
)

    Note: List.IsEmpty returns FALSE for a column of null values since, e.g., {null, null} is not the same as {}.

    • one_eon's avatar
      one_eon
      Frequent Visitor
      4 years ago

      Wow, that fixed it.  Thank you.  And also thanks for the tip about "List.IsEmpty".  I'm sure that would have been my next question.

      I had tried declaring a variable with 'let' but that hadn't worked either.  I guess I have to learn more about when to use variables and when to use functions in power query, as I have a hard time telling the difference between the two.

      Not all the tables successfully rendered, as a few of them have a "DataFormat.Error: Invalid cell value '#DIV/0!'." But I suspect that is due to something in the file itself.  I will try to replace the errors in power query and run it again.

       

      Again, thank you.