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!
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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
    Icon for Community Support rankCommunity 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!
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks you v-kelly-msft 

       

      i understand the solution but i am still looking for win m-code, where we can filter null from columns atleast from one column where i have date

      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        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!