We cannot convert a value of type Record to type Table.

[Anonymous]

I am new to PowerBI coming in with a dev background including PL/SQL, R, etc. Trying to do a real simple thing, divide the value of one column by another and create a new column. Created the formula in the Add Custom Column dialog (clearly says no errors) and this is from the Advanced Editor:

let
    Source = <custom connector source>,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"dailyHours", type number}, {"effectiveHours", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "calc", each [effectiveHours] / [dailyHours])
in
    #"Added Custom"

When I try to Apply, it throws the error: "We cannot convert a value of type Record to type Table."

From my perspective I am creating a new column based on values of existing columns. For me, a record is a row and a table is a set of rows and columns, so I'm having a real hard time deciphering this.

What might be the problem here?

How does one debug this?

TIA,

Jay

[RolandsP]

Can you please share a printscreen from Query Editor when you have selected last step 'Added Custom'? 

[d_gosbell]

Sorry, disregard my earlier post. Square brackets can either be used for records or referencing columns. So the following should work fine

[effectiveHours] / [dailyHours]

When you create a record object you use the [field = value] syntax, but that is not the case here.

When you preview the results in the query editor does each step return results?

[Anonymous]

Thank you for replying. 

Yes, it returns results in preview and they are as expected. This is why I am puzzled since every step except the last Apply step appears to work fine. The error makes no sense to me. 

I am actually reproducing an existing column which I would normally aggregate and I created the effectiveHours field specifically to simplify the aggregation in Power BI.

If there is a null or dailyHours == 0 in the data, could this be a cause? ie: if the numerator is null I would expect some form of NaN error, or if dailyHours was 0, a divide by sero error. I'm going to try with a tiny dataset and see how that goes.

Thanks,

Jay

[Anonymous]

These grabs shows the resulting field after applying percentage format and the selection dialog shows it actually handles bad values producing 0 or NaN result which is totally fine. I am even able to format the field as a percentage. Still throws the same error on Apply.

Pardon the edited post, getting used to the forum.

[Anonymous]

I have done a simple test with a limted dataset and having removed all rows with null values and the new column works as defined.

I presume I will need to look at defining a function or something to trap nulls and prevent the calculation attempt. 

Thank you both for replying!

Regards,

Jay

[d_gosbell]

In the M language used in queries square brackets are used to denote records. Unlike DAX where square brackets are used to reference measures/columns.

So I think replacing the following expression in the Custom Column dialog

[effectiveHours] / [dailyHours]

with the following should fix your issue

effectiveHours / dailyHours