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!
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!