Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Doesn'r matter if you have Null value in your current data or not, you can "Remove Empty"
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.
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
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.