Forum Discussion
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: 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!
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 InsertedYearBest Regards
MaggieCommunity 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.
7 Replies
Hi Anonymous ,
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.
NathanielI 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.
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.
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
MaggieCommunity 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.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 InsertedYearIs that the right M code to remove rows where column 'submitted' contains null?
Thanks!
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 InsertedYearBest Regards
MaggieCommunity 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.
