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 ...
Need Help
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

       

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

      Hi Bakhtawar ,

      Have you solved the problem?

      If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

      If you still need help, please feel free to ask.

      Best  Regards,

      Cherry

       

      • Bakhtawar's avatar
        Bakhtawar
        Post Patron
        7 years ago

        no.. i did not understand about change type