Forum Discussion

Bakhtawar's avatar
Bakhtawar
Post Patron
7 years ago

Remove null values from data

I have 20 + tables and also there is a lot more columns so this is not possible to filter out null values one by one on each column so is there any alternative to remove null values from whole dataa

 

any help 

Need Help

14 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi Bakhtawar ,

    By my tests, we could achieve that filter all null values for mutiple columns at one time with the M Query below.

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each List.Contains(Record.ToList(_),null)=false)

    Here is the output.

    I'm not good at M query, may ImkeF will have the better solutions. 

    Best Regards,

    Cherry

     

    • ImkeF's avatar
      ImkeF
      Community Champion
      7 years ago

      Hi Cherry (v-piga-msft ),

      you've obviously learned a lot M meanwhile - cannot think of a better solution than this ;)

    • Bakhtawar's avatar
      Bakhtawar
      Post Patron
      7 years ago
      can i paste this as it is or i should do some modifications? Table.SelectRows(#"Changed Type", each List.Contains(Record.ToList(_),null)=false)
      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi Bakhtawar ,

        If you provious step is "Changed type", you could copy my query and paste it in Advanced Editor.

        Best  Regards,

        Cherry