Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter out null values

I am trying to create a dynamic calendar table, but some of the rows will have null for the date value and it is throwing an error. How do I remove those?  This is the error:   Expression.Error: W...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    I can reproduce your problem

     

    To slove this problem, change the code as below

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBLDsAgCETv4tpEoLXKWYwLe/9D9JMQCLIjPIYZGCOtlBMXLATIb9mlnFkYKSPPDmG8605hpleND3jBZeC2rSlsnvXAiTUZ+3mxjmMgBuvQfAGrSu7tfeAhxWd98PeYDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, submitted = _t, submitted_to_sponsor_date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{"submitted", type date}),
    MaxDate = Record.Field(Table.Max(Table.SelectRows(#"Changed Type", each [submitted] <> null and [submitted] <> ""), "submitted"),"submitted"),
    MinDate = Record.Field(Table.Min(Table.SelectRows(#"Changed Type", each [submitted_to_sponsor_date] <> null and [submitted_to_sponsor_date] <> ""), "submitted_to_sponsor_date"),"submitted"), 
    DaysElapsed = Number.From(MaxDate-MinDate),
    DatesList = List.Dates(MinDate, DaysElapsed+1,Duration.From(1)),
    RawDatesTable = Table.FromList(DatesList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    ChangedType1 = Table.TransformColumnTypes(RawDatesTable,{{"Date", type date}}),
    InsertedDay = Table.AddColumn(ChangedType1, "Day", each Date.Day([Date]), type number),
    InsertedMonth = Table.AddColumn(InsertedDay, "Month", each Date.Month([Date]), type number),
    InsertedYear = Table.AddColumn(InsertedMonth, "Year", each Date.Year([Date]), type number)
in
    InsertedYear
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous 

Before first step(Change type), replace null in "submitted" column with "9999/1/1"

If it doesn't help, please share me some sample data so that i can reproduce your problem.

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I tried the following, but I'm still getting the same error. I added this line:

    RemovedNulls = Table.RemoveMatchingRows(proposal,{[submitted=null]}),

    to get this:

     

    = let
RemovedNulls = Table.RemoveMatchingRows(proposal,{[submitted=null]}),
ChangedType = Table.TransformColumnTypes(RemovedNulls,{{"submitted", type date}}), 
MaxDate = Record.Field(Table.Max(ChangedType, "submitted_to_sponsor_date"),"submitted"), 
MinDate = Record.Field(Table.Min(ChangedType, "submitted"),"submitted"), 
DaysElapsed = Number.From(MaxDate-MinDate),
DatesList = List.Dates(MinDate, DaysElapsed+1,Duration.From(1)),
RawDatesTable = Table.FromList(DatesList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
ChangedType1 = Table.TransformColumnTypes(RawDatesTable,{{"Date", type date}}),
InsertedDay = Table.AddColumn(ChangedType1, "Day", each Date.Day([Date]), type number),
InsertedMonth = Table.AddColumn(InsertedDay, "Month", each Date.Month([Date]), type number),
InsertedYear = Table.AddColumn(InsertedMonth, "Year", each Date.Year([Date]), type number)
in
    InsertedYear

    Is that the right M code to remove rows where column 'submitted' contains null?

     

    Thanks!

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous 

      I can reproduce your problem

       

      To slove this problem, change the code as below

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBLDsAgCETv4tpEoLXKWYwLe/9D9JMQCLIjPIYZGCOtlBMXLATIb9mlnFkYKSPPDmG8605hpleND3jBZeC2rSlsnvXAiTUZ+3mxjmMgBuvQfAGrSu7tfeAhxWd98PeYDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, submitted = _t, submitted_to_sponsor_date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{"submitted", type date}),
    MaxDate = Record.Field(Table.Max(Table.SelectRows(#"Changed Type", each [submitted] <> null and [submitted] <> ""), "submitted"),"submitted"),
    MinDate = Record.Field(Table.Min(Table.SelectRows(#"Changed Type", each [submitted_to_sponsor_date] <> null and [submitted_to_sponsor_date] <> ""), "submitted_to_sponsor_date"),"submitted"), 
    DaysElapsed = Number.From(MaxDate-MinDate),
    DatesList = List.Dates(MinDate, DaysElapsed+1,Duration.From(1)),
    RawDatesTable = Table.FromList(DatesList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    ChangedType1 = Table.TransformColumnTypes(RawDatesTable,{{"Date", type date}}),
    InsertedDay = Table.AddColumn(ChangedType1, "Day", each Date.Day([Date]), type number),
    InsertedMonth = Table.AddColumn(InsertedDay, "Month", each Date.Month([Date]), type number),
    InsertedYear = Table.AddColumn(InsertedMonth, "Year", each Date.Year([Date]), type number)
in
    InsertedYear
      Best Regards
      Maggie
      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.