Removing columns containing only zeros

[tc5pt]

I have a cash flow timeline and some columns (representing years) have values of 0 throughout the entire column. There are some columns that contain 0s, but also contain other numbers, which I need to keep.

So I am looking to remove columns that contain only a certain value, in this case 0.

[v-juanli-msft · Accepted solution]

Hi @tc5pt 

As tested, the methods provided by this article should be helpful.

Automatically Remove Empty Columns And Rows From A Table In Excel Using Power Query

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FredLEGUEN]

Hi @Nolock 

This code will remove all your empty columns

Col1  Col2  Col3  Col4

   5           7         

  12          10      

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col 1", Int64.Type}, {"Col 2", type any}, {"Col 3", Int64.Type}, {"Col 4", type any}, {"Col 5", Int64.Type}, {"Col 6", type any}, {"Col 7", Int64.Type}}),
    Unpivoted = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    ColumnName = Table.Distinct(Unpivoted, {"Attribute"})[Attribute],
    Custom1 = Table.SelectColumns(Source, ColumnName)
in
    Custom1

Replace your cells with a 0 by null and like that you will be able to remove your columns with 0

[Nolock]

Hi @tc5pt,

you can do that in the PowerQuery Editor UI as shown on the screenshots below.