cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kman42
Helper III
Helper III

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!

1 ACCEPTED SOLUTION

Hi @kman42 

I can reproduce your problem

Capture5.JPGCapture6.JPGCapture7.JPG

 

To slove this problem, change the code as below

Capture9.JPG

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.

View solution in original post

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

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.

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!

Hi @kman42 

I can reproduce your problem

Capture5.JPGCapture6.JPGCapture7.JPG

 

To slove this problem, change the code as below

Capture9.JPG

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.

View solution in original post

Nathaniel_C
Super User I
Super User I

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

filter null.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors