Filter out null values

[kman42]

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: We cannot convert the value null to type Date.
Details:
    Value=
    Type=[Type]

This is the code:

= let
ChangedType = Table.TransformColumnTypes(proposal,{{"submitted", type date}}), 
MaxDate = Record.Field(Table.Max(ChangedType, "submitted"),"submitted"),
MinDate = Record.Field(Table.Min(ChangedType, "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

"submitted" is the date column I'm working with. I think the error is in the first line, but I swiped this code from Collie's book, so I don't completely understand it.

Thanks!

[zaraheyd]

Doesn'r matter if you have Null value in your current data or not, you can "Remove Empty" 

[v-juanli-msft]

Hi @kman42 

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.

[kman42]

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

Hi @kman42 

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.

[Nathaniel_C]

Hi @kman42 ,

In Power Query, filter the column with the nulls by clicking on the arrow at the top of the column. I got that from @KenPuls  book.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[kman42]

I think that would work, but I need all the rows in the 'proposal' table for other purposes. I could copy the proposal table and then filter it and then run the query to create the Calendar table, but that seems like it would just clutter up the data model. I'd prefer to do it with the code using the unaltered proposal table as the source to keep things simple. It seems like it must be possible; I just don't know where to start with the code.

[AnkitBI]

Can you share sample data or PBIX file? I am not able to Recreate the issue. It is working even with null/blank values in columns.

Also, to confirm which step is failing, please go through Applied Steps in Query Settings from top. You will know which step is failing.