Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

remove "null" cell from columns

Hi Super Users,

 

How we can filter or remove null cell crom column while extracting headers from table column in power query

 

Need Help
Show and Tell
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ,

     

    You can use  a M-code such as below:

     

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

     

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Should be able to click on the drop down for the column and remove (filter out) the nulls/blanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you!

      but it should be dynamically filter in query editor not more adding step. i have date columns where null valued are there and my dataflow is not getting refresh it. hence seeking for help

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        6 years ago

        Hi Anonymous ，

         

        I‘m a little confused about "dynamical filter" and "no more adding step".

        But as for "Remove null cells from columns while extracting the headers",you can go to query editor,then select all the column>click on "unpivot columns":

        And you will see:

        Finally click on the dropdown botton of column value >unselect "blank":

         

        All the null cells have been removed.

         

        Best Regards,
        Kelly
        Did I answer your question? Mark my post as a solution!