Excel 2021 - filter function to only include columns with data

[BrerRabbit]

I have a table with alot of data and alot of empty columns.

Can I filter where only those columns with data are included? In other words to summarise and condense the table using the filter function?

Name	School	Art Course	Science Course	Maths Course	English Course
Dorothy	New York		4.3		5.9
Tim	Los Angelos		8.2		7
Alan	Boulder		6		3.8

 

[ExcelToDAX]

Assuming your table is called Table1, you can do this in Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    ColumnHeaderColumn = Table.SelectColumns(#"Unpivoted Columns",{"Attribute"}),
    ColumnsToKeep = Table.ToList(Table.Distinct(ColumnHeaderColumn)),
    OriginalSource = Source,
    RemovedNullColumns = Table.SelectColumns(OriginalSource,ColumnsToKeep)
in
    RemovedNullColumns