Formula to Automatically Remove Blank Columns from a table

[one_eon]

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:

Data	File Name
Table	File1.xlsx
Table	File2.xlsx
Table	File3.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.

[AlexisOlson · Accepted solution]

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]

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.