How to select and delete columns that have all zeros or all blanks

[JollyRoger01]

I have the following table that I would like to delete columns from if those columns contain all zeros or all blanks. To try and learn M language, I'd like to try and find out how to select the columns first and then delete them because that logic makes sense to me. Can someone please let me know how I can use Table.SelectColumns to select these columns, and then another step for deleting them?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBLCsAgDETvkrWL3kdcVK2Cn/tvS3Aa0hKh4IOBeZEQ7+kgR+cGCm4JcYMIbCeD1w/ZQATjPRV7F4gq69kCksrSs1xBVlkEthvQ8cdy7HVcoCD3z+zA4hV56J7lic0b8jp+uAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"20201218" = _t, #"20201221" = _t, #"20201222" = _t, #"20201223" = _t, #"20210104" = _t, #"20210105" = _t, #"20210106" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"20201218", type text}, {"20201221", type text}, {"20201222", type text}, {"20201223", type text}, {"20210104", type text}, {"20210105", type text}, {"20210106", type text}})
in
    #"Changed Type"

[Vera_33 · Accepted solution]

Hi @JollyRoger01 

Me again. You want it?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBLCsAgDETvkrWL3kdcVK2Cn/tvS3Aa0hKh4IOBeZEQ7+kgR+cGCm4JcYMIbCeD1w/ZQATjPRV7F4gq69kCksrSs1xBVlkEthvQ8cdy7HVcoCD3z+zA4hV56J7lic0b8jp+uAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"20201218" = _t, #"20201221" = _t, #"20201222" = _t, #"20201223" = _t, #"20210104" = _t, #"20210105" = _t, #"20210106" = _t]),
    Custom1 = Table.ColumnNames(  Source),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each [a=Table.Column(Source, [Column1]),
b=List.MatchesAll( a, each _ ="0") or List.MatchesAll( a, each _ ="")][b]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = false)),
    Custom2 = Table.SelectColumns(Source,  #"Filtered Rows"[Column1])
in
    Custom2

[JollyRoger01]

Thank you Vera. Just so I can follow the code, would you please be able to confirm or correct my thinking below?

Custom1

Convert the column names into a list.

Converted to Table

Convert the list to a table (I don't really follow the function syntax here, a little confusing, but I can get by).

Added Custom

Added a column called Custom. Here I am very confused. Where it says:

each [a=Table.Column(Source, [Column1])

 does this mean it is doing a for loop, like for each column in the Source table where the header has the same value as Column1, then do this...?

And then inside that for loop, where it says:

b=List.MatchesAll( a, each _ ="0") or List.MatchesAll( a, each _ ="")

is it doing another for loop to find the column name where all the values (_) of the current Source table column equal 0 or "", then recording the TRUE/FALSE values as a list?

And then at the end it returns this list as the column?

[Vera_33]

Hi @JollyRoger01 ,

To answer your questions, so you get your Source column names in Column1, then add a custom column to call each column from Source Table as a list (which is a, I am lazy, so always abc or xyz...you can name them more meaningful), step b is to check if all items in a is 0 or blank, then you filter it out to get the Column names which are not all 0 or blank. Hope it is clear?