Forum Discussion

tc5pt's avatar
tc5pt
Regular Visitor
7 years ago
Solved

Removing columns containing only zeros

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.

Need Help

3 Replies

  • Nolock's avatar
    Nolock
    Resident Rockstar
    7 years ago

    Hi tc5pt,

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

     

  • FredLEGUEN's avatar
    FredLEGUEN
    Helper III
    7 years ago

    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