remove "null" cell from columns

[Amardeep100115]

Hi Super Users,

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

[Greg_Deckler]

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

[Amardeep100115]

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]

Hi @Amardeep100115 ，

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!

[Amardeep100115]

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 · Accepted solution]

Hi @Amardeep100115 ,

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!