Remove null values from data

[Bakhtawar]

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 

[v-piga-msft]

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

[Bakhtawar]

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]

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]

no.. i did not understand about change type

[v-piga-msft]

Hi @Bakhtawar ,

This is the step "Change type".

You could copy and paste my code after the "Change type " step.

Best Regards,

Cherry

[Bakhtawar]

so this is for whole table  or only for 1 column

[v-piga-msft]

Hi @Bakhtawar ,

My code from my first reply is for the whole table.

Have you tried it in your advanced editor?

Best  Regards,

Cherry

[Bakhtawar]

from your first i saw this 

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

now i want to ask what is here changed type here .. here i replace with my table name from change type or something else 

[v-piga-msft]

Hi @Bakhtawar ,

Here is my complete code in Advanced Editor.

let
    Source = Excel.Workbook(File.Contents("C:\Users\cherryg\Desktop\Book1.xlsx"), null, true),
    Sheet22_Sheet = Source{[Item="Sheet22",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet22_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"value", Int64.Type}, {"Date_1", type date}, {"Sales", Int64.Type}, {"Wegnr.", type text}, {"Groengebied", type text}, {"Elementnummer", Int64.Type}, {"Oppervlakte (m²)", type number}, {"Lengte (m)", type number}, {"Id", Int64.Type}, {"Number", Int64.Type}, {"Codes", Int64.Type}, {"Wegnr", type text}, {"Element", type text}, {"Van_HM", type number}, {"Tot_HM", type number}, {"Dwarsprofiel", type text}, {"Opp. m²", Int64.Type}, {"Lengte m1", Int64.Type}, {"Beheertype", type text}}),
    #"Filter rows"=Table.SelectRows(#"Changed Type", each List.Contains(Record.ToList(_),null)=false)
in
    #"Filter rows"

Please modify your code based on your scenario.

Best Regards,

Cherry

[Bakhtawar]

@v-piga-msft  ?? help plz

[Bakhtawar]

HI this is what i supposed to asking what i replace here "

Changed Type

table name or something else 

[ImkeF]

Hi @Bakhtawar ,

if you have trouble integrating the sample code into your solution, you might check out this video: http://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-co... 

[v-piga-msft]

Hi @Bakhtawar ,

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

Best  Regards,

Cherry

[ImkeF]

Hi Cherry (@v-piga-msft ),

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