excelvbapowerbipowerquerym

M Code(Power Query) to Remove Empty columns that runs fast


I use the below code in Power Query to remove empty columns from a table with a lot of columns. It runs very slow and I am looking for a way to speed it up. Basically if all the entries in a given column is null, the column should be removed

//Remove Empty Columns
ColumnstoKeep = List.Select(
        Table.ColumnNames(#"Expanded"),each List.NonNullCount(Table.Column(#"Expanded",_)) <>0 ),

RemoveEmptyColumns = Table.SelectColumns(#"Expanded",ColumnstoKeep),

Solution

  • //Remove Empty Columns
    bufferedTable = Table.Buffer( #"Expanded"),
    ColumnstoKeep = List.Select(
            Table.ColumnNames(#"bufferedTable"),each List.NonNullCount(Table.Column(#"bufferedTable",_)) <>0 ),
    
    RemoveEmptyColumns = Table.SelectColumns(#"bufferedTable",ColumnstoKeep),