Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChrisBroome
Frequent Visitor

Custom Column error with Missing Data

Hi,

 

I currently have a column 'Done' where Rows contain either a date, or are blank. I'm creating a new custom column that turns these dates into 'week commencing' dates. my query so far is;

 

#"Inserted Start of Week" = Table.AddColumn(#"Filtered Rows", "Start of Week", each Date.StartOfWeek(DateTime.From([Done])), type datetime)
in
#"Inserted Start of Week"

 

This works for those rows with dates, but throws errors where the row is blank. My thought was to include an 'otherwise null' expression, but whatever I try results in either a 'token literal expected' or 'token comma expected' syntax error.

 

Can anyone help?

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @ChrisBroome 

Try this

 

= Table.AddColumn(#"Filtered Rows", "Start of Week",  each try Date.StartOfWeek(DateTime.From([Done])) otherwise null)

 

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

2 REPLIES 2
PhilipTreacy
Super User
Super User

Hi @ChrisBroome 

Try this

 

= Table.AddColumn(#"Filtered Rows", "Start of Week",  each try Date.StartOfWeek(DateTime.From([Done])) otherwise null)

 

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


@PhilipTreacy 

Doh, obvious now! 

 

thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors