How do I have Power BI intelligently delete blank columns?

[Anonymous]

I have a series of columns like this:

I would like to intelligently delete any column (i.e. Class #4) that is completely blank without having to do so manually.

The only solution I could devise was unpivot the columns and do multiple group-bys before deleting all rows containing blanks. After that, however, I get stuck because I cannot pivot the unpivoted columns due to the "column names" containing duplicates.

I even tried transposing, but that obviously just creates multiple columns for Class #3, which is NOT what I want unless there is any easy way to combine duplicate columns.

Any suggestions would be greatly appreciated. 

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can use 'unpivot column' feature to transform your table, then use 'selected row' function to remove empty rows.

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRSkxJBZFlILZSrE60EogGCaSiiRRDVCrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Class 1" = _t, #"Class 2" = _t, #"Class 3" = _t, #"Class 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Class 1", type text}, {"Class 2", type text}, {"Class 3", type text}, {"Class 4", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> ""))
in
    #"Filtered Rows"

Regards,

Xiaoxin Sheng