Request: Expression.Error: The Field of the record wasn't found

[frw]

Hello. First time poster here. I'm fairly new to PowerBI but not to data manipulation in other languages, and I'm having an issue that I know should be fairly easy to figure out but can't quite wrap my head around. I've searched around a bit but haven't seen any answers that quite get at what I'm looking for.

TalkTime	HoldTime
26:32:53	01:46:32

Essentially, I'm working with an imported csv that has several columns representing cumulative call durations in the format hh:mm:ss in text format. I can't use the "duration" type because many of these are longer than 24 hours, which causes an error. Because of this, I'd like to convert the time to minutes in a new column, splitting the text by delimiter and then doing math to get everything into minutes. 

When I invoke the following function in the editor, I get the correct values. 

= (sourceTable as table, columnName as text) =>

let
    #"Split Column by Delimiter" = Table.SplitColumn(sourceTable, columnName, Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"hours", "minutes", "seconds"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"hours", Int64.Type}, {"minutes", Int64.Type}, {"seconds", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "TimeInMinutes", each [hours] * 60 + [minutes] + [seconds] / 60),
    Calculation = Record.Field(#"Added Custom1"{0}, "TimeInMinutes")
in
    Calculation

When I try to add a new custom column to the existing table, however, I get the following error:

Column code:
= Table.AddColumn(#"Added Custom1", "TalkTimeMin", each fnTimeToMinutes(Source, [TalkTime]))

Error code:
Expression.Error: The field '26:32:53' of the record wasn't found.

 I'm not quite sure why the function works when invoked but not when adding columns. Any ideas as to why this is?

Thanks in advance.

[jennratten]

Just to confirm, the desired result would just be adding a new column to the table that contains the TalkTime value in minutes?  If that's right, are you wanting a whole number or a decimal?

[frw]

I could have explained that better, but you are correct. A new column that will have a decimal value conversion of the TalkTime for each row of the table. 

[jennratten]

How about this?  The talk time has been added as a decimal representing the number of days, which is how duration is represented when loaded to the data model.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKzMjayMjVW0lEyMLQyAfGUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TalkTime = _t, HoldTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TalkTime", type text}, {"HoldTime", type time}}),
    Days = Table.AddColumn(#"Changed Type", "Days", each Number.IntegerDivide(Number.FromText(Text.BeforeDelimiter([TalkTime], ":")), 24), type number),
    Hours = Table.AddColumn(Days, "Hours", each Number.FromText(Text.BeforeDelimiter([TalkTime], ":")) - ([Days]*24), type number),
    Minutes = Table.AddColumn(Hours, "Minutes", each Number.FromText(Text.BetweenDelimiters([TalkTime], ":", ":")), type number),
    Seconds = Table.AddColumn(Minutes, "Seconds", each Number.FromText(Text.AfterDelimiter([TalkTime], ":", 1)), type number),
    TalkTimeAsDecimal = Table.AddColumn(Seconds, "TalkTimeAsDecimal", each Duration.TotalDays ( #duration ( [Days], [Hours], [Minutes], [Seconds] ) ), type number )
in
    TalkTimeAsDecimal

[frw]

Thanks again for your help so far. This does work, but the end result of adding a "Custom Column" while invoking this function results in a column containing Table entries instead of just the values. When I drill down into the table, the correct calculated value is there. I guess what I'm wondering is how to  avoid that drill down step: can I just use "Custom Column" and a function to return just a signle column with the correct values instead of the table + drill down?

Formula for adding column: = Table.AddColumn(#"Added Custom", "Custom", each fnTimeToMinutes(#"Added Custom","TalkTime"))

I was able to take what you gave me and make it dynamic to handle other columns that may be passed. I can get the function to return a list of the values (replacing "Table" from the screenshot above), but getting one correct entry per row of the table is driving me mad.

[jennratten]

Oh - the solution I added was the entire example.  To incorporate this into your specific query, you need to add the custom columns below, being sure to update the table referenced in the first line with the name of your last step.  If you will post a snip of the advanced editor for your query I can explain how to integrate this exactly.

    Days = Table.AddColumn(#"Your Last Step Here", "Days", each Number.IntegerDivide(Number.FromText(Text.BeforeDelimiter([TalkTime], ":")), 24), type number),
    Hours = Table.AddColumn(Days, "Hours", each Number.FromText(Text.BeforeDelimiter([TalkTime], ":")) - ([Days]*24), type number),
    Minutes = Table.AddColumn(Hours, "Minutes", each Number.FromText(Text.BetweenDelimiters([TalkTime], ":", ":")), type number),
    Seconds = Table.AddColumn(Minutes, "Seconds", each Number.FromText(Text.AfterDelimiter([TalkTime], ":", 1)), type number),
    TalkTimeAsDecimal = Table.AddColumn(Seconds, "TalkTimeAsDecimal", each Duration.TotalDays ( #duration ( [Days], [Hours], [Minutes], [Seconds] ) ), type number )

[frw]

= (sourceTable as table, columnName) =>

let
    Source = sourceTable,
    Days = Table.AddColumn(Source, "Days", each Number.IntegerDivide(Number.FromText(Text.BeforeDelimiter(Record.Field(_,columnName), ":")), 24), type number),
    Hours = Table.AddColumn(Days, "Hours", each Number.FromText(Text.BeforeDelimiter(Record.Field(_,columnName), ":")) - ([Days]*24), type number),
    Minutes = Table.AddColumn(Hours, "Minutes", each Number.FromText(Text.BetweenDelimiters(Record.Field(_,columnName), ":", ":")), type number),
    Seconds = Table.AddColumn(Minutes, "Seconds", each Number.FromText(Text.AfterDelimiter(Record.Field(_,columnName), ":", 1)), type number),
    TalkTimeAsDecimal = Table.AddColumn(Seconds, columnName&"Days", each Duration.TotalDays ( #duration ( [Days], [Hours], [Minutes], [Seconds] ) ), type number )
in
    TalkTimeAsDecimal

Here's the code for the function. As it is, it looks like it handles column names as variables, and it is returning the right calculation.

This is what the step looks like in the Advanced Editor for the query.

#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each #"fnTimeToMinutes (2)"(#"Added Custom","HandleTime"))

 I'm still seeing the issue from above though where the actual added column displays "Table" for each entry. I can drill down and get the right result (below), and I'm fine doing that drill down for each step if need be.

[jennratten]

I didn't realize you were putting this inside a custom function that was being invoked from within an add column step.  Please change your step script to this:

#"Added Custom1" = #"fnTimeToMinutes (2)"(#"Added Custom","HandleTime")

[frw]

Aha! Thank you! I think I understand the difference between what I had down vs what you shared right there.

I appreciate your patience here, especially because I wasn't quite giving you all the information needed :). I'll go ahead and mark your original comment as solved, as it answered the original question well.

Cheers!

[jennratten · Accepted solution]

Hello - you are getting this error because the 2nd argument of your custom function is looking for a column name and when you are invoking the function you are passing it a value from the current row being iterated.  Please try this instead (the only thing I changed was I replaced the square brackets with double quotation marks).

= Table.AddColumn(#"Added Custom1", "TalkTimeMin", each fnTimeToMinutes(Source, "TalkTime"))

[frw]

I appreciate the quick answer here. Replacing the brackets with quotation marks happened to solve my Expression.Error issue, but now the function returns one static value (from the {0}, I'm assuming) for each row of the table instead of the calculated value for each row. If I remove that final Calculation line and return just the last step, Table.AddColumn adds an entire list to each row of the original table.

Is there an easier way/different method to have the function return a single entry for each row or, similarly, append the list to the original table as a new column?