In lesson 6.11 concatenating "00:" to the values in the gap column returns nulls where the durations are one hour or more. This is true in Excel 2010, Excel 2013 and Excel 2016. One solution is to use an if statement to only concatenate "00:" to the values in the gap column where the durations are less than one hour. This can be done by using the Text.Length function to test for a length of less than six.
Hi Joseph,
Yep, there are lots of ways to tackle some of the data cleaning exercises you might have. The purpose of lesson 6.11 was to teach error trapping so hopefully you've mastered that too.
Mynda
Hi, dont mind if i wish to clarify my concern below about section 6:11
Try ...Otherwise vs If then else
could anyone tell me the difference? Then look quite similar whereby excel could provide us the desired solutions of each scenarios.
when should i use "Try..otherwise?" but not "If then else" and via versa?
Thanks.
Hi Tan,
Try..otherwise is designed to trap errors. It's like IFERROR in terms of equivalent Excel functions. More on error handling in Power Query here.
Mynda
