Forum Discussion

JollyRoger01's avatar
JollyRoger01
Icon for Helper III rankHelper III
5 years ago
Solved

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

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 colum...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
JollyRoger01
Icon for Helper III rankHelper III
5 years ago

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?

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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?