Forum Discussion
use column name in custom column logic
I need to populate an hour column with a zero when an employee is clocked in but the count is blank. So the 12:00 and 15:00 hours need to be populated with a zero on its row.
| ID | Kronos Clock In Hour | Kronos Clock Out Hour | 12:00 | 13:00 | 14:00 | 15:00 |
| 10082 | 8/16/2022 4:00 | 8/16/2022 11:00 | ||||
| 10082 | 8/16/2022 12:00 | 8/16/2022 15:00 | 4 | 6 |
This is what I need:
| ID | Kronos Clock In Hour | Kronos Clock Out Hour | 12:00 | 13:00 | 14:00 | 15:00 |
| 10082 | 8/16/2022 4:00 | 8/16/2022 11:00 | ||||
| 10082 | 8/16/2022 12:00 | 8/16/2022 15:00 | 0 | 4 | 6 | 0 |
I would prefer to do this in a query rather than using an Excel formula. So far this is my progress
Hi amcneil32 ,
For such reporting errors, you can use Date.ToText() and Number.ToText() to convert public notices.
just like below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI10TUyUNJRMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value1 = _t, Value2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value1", type date}, {"Value2", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.ToText([Value1])&"-"&Number.ToText([Value2])) in #"Added Custom"
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- claymcooper
Resolver II
Would it work of you just used the replace values tool and replaced null with 0 inside Power Query?
v-henryk-mstfCommunity Support
Hi amcneil32 ,
For such reporting errors, you can use Date.ToText() and Number.ToText() to convert public notices.
just like below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI10TUyUNJRMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value1 = _t, Value2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value1", type date}, {"Value2", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.ToText([Value1])&"-"&Number.ToText([Value2])) in #"Added Custom"
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.amcneil32 would you mind sharing how you connected your Kronos data? Was it using the REST API an if so how did you do this?
Anonymous , I am pulling Kronos data from a data warehouse. I dont have much access with this particular DW, just a few tables, so i'm not too sure of the details behind the data migration.
