locked
Type check error in List + Record RRS feed

  • Question

  • List.Transform(#table({"d"}, {{#table({"d1"}, {{3}})}})[d], each Record.Field(_, "d1"))

    The operator above doesn't throw type error In PBID, despite the fact that we sent a table as an argument to the Record.Field.

    But it should to throw type error, because Record.Field must accept only records as an argument. E.g. this code

     Record.Field(#table({"d1"}, {{3}}), "d")

    throws an error as expected.

    And in Excel the first code throws expected exception. So it is an error in PBID.


    Friday, July 19, 2019 1:46 PM

Answers

  • Andrey, I think it's fair to say that this is a bug.

    When the user query contains a predicate or transformation which is applied to a list or table, we try to normalize it so that the folding code doesn't have to handle as many variations. Both Record.Field and Table.Column get normalized into the field access operator. This means that List.Transform(list, each Record.Field(_, "A")) and List.Transform(list, each Table.Column(_, "A")) both get transformed into List.Transform(list, each [A]). In your case, of course, no folding happens -- but now we have a different expression. The new expression is recompiled and then invoked without regard for the original one. Field access is a primitive that works against both records and tables, so you get a result instead of an error.

    The root cause for this is probably that the table type is a relatively late addition to the product. Originally tables were just lists of records, but this approach turned out to have some drawbacks.

    Monday, July 22, 2019 3:53 PM

All replies

  • The expression is correct.

    #table({"d"}, {{#table({"d1"}, {{3}})}})[d] returns a list with 1 table.

    Each row of a table evaluates to a record value. So we are passing [d1 = 3] to record field.

    Therefore Record.Field(_, "d1") = Record.Field([d1 = 3], "d1") = 3, which is the result returned.

    Friday, July 19, 2019 5:32 PM
  • Hi Colin!

    No, the expression is not correct.

    While there is no particular description of the field access to a value of table type in the Microsoft Power Query M Formula Language Specification, there are some clues which helps to understand, that a table is just a list (3.6, 4.13 of the doc) of rows, which are records of closed records type (5.6).

    So, if we do a "field" access to a table, we do a field access to every record of a list of rows of a table, so as a result we get a list of values of the field of every row of a table. You can easily see that if you do field access to a table in PQ UI.

    So in my example we get the list of elements of record type (aka table), do a field access to the field of each the element (i.e. record), and get a value of the field, which is of table type. And the latter value is passed as the argument to each expression.

    You can check the type of the parameter via this syntax, which perfectly works:

    = List.Transform(#table({"d"}, {{#table({"d1"}, {{3}})}})[d], (x as table) => x)

    While this code throws an error:

     
    = List.Transform(#table({"d"}, {{#table({"d1"}, {{3}})}})[d], (x as record) => x)

    Interestingly, if you use that synaxis, you "wake up" M for type checking of Record.Field operator input parameter :-), so it throws an error:

    = List.Transform(#table({"d"}, {{#table({"d1"}, {{3}})}})[d], (x as table) => Record.Field(x, "d1"))


    Friday, July 19, 2019 6:47 PM
  • Hi Andrey,

    While there is no particular description of the field access to a value of table type in the Microsoft Power Query M Formula Language Specification, there are some clues which helps to understand, that a table is just a list (3.6, 4.13 of the doc) of rows, which are records of closed records type (5.6).

    A table can be constructed from a list or record, but a table is a type and has behaviors very different from a list or record. So a table is more that just a list of rows. A specific evaluation on a table could produce a list, record or even a scalar value. Obviously, when we are iterating a table, we are evaluating each row, which results in a record value (a row is not a type - every expression must return a valid M type).

    So, if we do a "field" access to a table, we do a field access to every record of a list of rows of a table, so as a result we get a list of values of the field of every row of a table. You can easily see that if you do field access to a table in PQ UI.

    Well, complex types are inerrable based on type - a table iteration is an evaluation of each row, a record iteration is an evaluation of each field, and a list iteration is an evaluation of each item. 

    I still think that the original expression is correct - by design. I believe what's happening is that in certain instances, the table is being implicitly converted to a list of records. Obviously, the presence of the Record.Field function contributes to the conversion taking place.

    = List.Transform(#table({"d"}, {{#table({"d1"}, {{3}})}})[d], (x as table) => Record.Field(x, "d1"))

    returns an error, but

    = List.Transform(#table({"d"}, {{#table({"d1"}, {{3}})}})[d], (x as any) => Record.Field(x, "d1"))

    works fine (like in the "each" case).

    So explicitly specifying x as table prevents this conversion from occurring, but x as any allows the conversion.

    The result returned {3} is exactly what we would expect from an implicit conversion.

    Also

    = List.Transform(#table({"d"}, {{#table({"d1"}, {{3}, {4}})}})[d], (x as any) => Record.Field(x, "d1"))

    returns {3, 4} - again exactly what we would expect from implicit conversion.

    As long as we can predict the result of an expression, it cannot be claimed to be incorrect.

    Friday, July 19, 2019 11:02 PM
  • I still think this warrants further discussion. I have made some code so to make the problem easier to understand and to apply my own thoughts to it. 

    let 
        InnerTable  = #table({"A"}, {{3},{4}}),
        OuterTable = #table({"B"}, {{InnerTable}}),
        
        //Works as expected 
        Transform1 =  Table.Column(InnerTable,"A"),
    
        //Returns an error, as expected
        Transform2 =  Record.Field(InnerTable,"A"),
    
        // Something to note: this type of notation works on both tables and records
        Transform3 = InnerTable[A], 
    
        //This works as expected
        TransformList1 = List.Transform(OuterTable[B],each Table.Column(_,"A")),
    
        //But this works as well!!! even though we are supplying table to first argument of Record.Feild which is table!!
        TransformList2 = List.Transform(OuterTable[B],each Record.Field(_,"A"))
    
    in
        TransformList2



    • Edited by CamWally Sunday, July 21, 2019 6:28 AM Making code comments clearer
    Saturday, July 20, 2019 4:35 AM
  •  //Does not work as expected
        Transform2
    =  Record.Field(InnerTable,"A"),

    No, works as expected. The first argument in Record.Field must be a record and we passed a table. Hence the error message.

    // Something to note: this type of notation works on both tables and records
        Transform3
    = InnerTable[A],

    Nothing new or unusual about that, since it's common syntax. For tables, the notation returns a list of values in column A. For records, the notation returns the value in field A.

    //But this works as well!!! even though we are supplying table to first argument of Record.Feild which is table!!
        TransformList2
    = List.Transform(OuterTable[B],each Record.Field(_,"A"))

    See my suggestion in the previous post as to why the expression works. 

    Saturday, July 20, 2019 8:03 PM
  • Hi Colin,

    My code comments maybe weren't clear and so I have edited the comment for Transform2 expression. I have also got rid of a previous edit, that you didn't comment on which was a code block at the bottom. 

    I have read both of your posts, a few times. One of the things you say is: "As long as we can predict the result of an expression, it cannot be claimed to be incorrect."

    I don't think it is that predictable

    If this should work:

    List.Transform(OuterTable[B],each Record.Field(_,"A")),

    Then why does this return an error?

    List.Transform({InnerTable},each Record.Field(_,"A"))

    You say that "in certain instances, the table is being implicitly converted to a list of records". I get that, that may be the reason why it happens but that doesn't mean its the correct behaviour. Also if we don't know what these "certain instances" are then we can't "predict the result of an expression". 

    Also, my comment about: 

    "Transform3 = InnerTable[A]"

    was bit of long shot theory, that maybe Table.Column and Record.Field were the same function in the backend but different types for the first argument were being enforced. And somehow this enforcement was being bypassed.

    Just did a little more work on this whole thing, with a slightly more complex example which people can play around with. 

    let

    InnerTable1  = #table({"A"}, {{3},{4}}),

    InnerTable2 = #table({"A"}, {{5},{6}}),

    OuterTable = #table({"B"},{{InnerTable1},{InnerTable2}}),

    //The original source of debate

    TransformList2 = List.Transform(OuterTable[B],each Record.Field(_,"A")),

    //These gives equivalent results

    TransformList3 = List.Transform({InnerTable1,InnerTable2},(Table as table)=> Table.Column(Table, "A")),

    TransformList4 = List.Transform({InnerTable1,InnerTable2}, (Table as table)=>List.Transform(Table.ToRecords(Table), (Rec as Record) => Record.Field(Rec,"A")))

    in 

    TransformList4




    • Edited by CamWally Sunday, July 21, 2019 7:38 AM
    Sunday, July 21, 2019 6:52 AM
  • Hi CamWally,

    I don't think it is that predictable

    If this should work:

    List.Transform(OuterTable[B],each Record.Field(_,"A")),

    Then why does this return an error?

    List.Transform({InnerTable},each Record.Field(_,"A"))

    Good point! My predictably hypothesis was not entirely correct :) However, there might be a deeper conspiracy at play, which doesn't make the first expression wrong.

    I'd like someone from the development team to chip in on this topic, but I suspect that the issue is a combination of type checking and lazy evaluation. When {InnerTable} is evaluated, it's likely that type checking occurs immediately. In the case of OuterTable[B], evaluation first produces the {InnerTable} before further evaluation takes place and perhaps this "delay" bypasses or avoids type checking.

    Predictability for the Record.Field evaluation appears to be the case in only one instance - A table where the evaluation of a column produces a list of one or more tables.

    Based on that narrow predictability, you've demonstrated that adding a second inner table (or as many as we want) works as "expected". ;)

    In the end, Andrey's discovery is an interesting one - I learned something new. I just took exception to the conclusion that the expression is wrong, without even asking the team why it works the way it does.


    Sunday, July 21, 2019 5:50 PM
  • First of all, let's not forget that we are talking about technical matters.

    That means that we should base all our discussions on the official technical documentation. Which consists of:
    I. "Power Query M language specification".
    II. "Types in the Power Query M formula language".
    III. "Power Query M function reference".

    If we watch some non-trivial things, dealing with M, and try to classify whether it's error or a feature, we must use the documentation for that. And if it is proven that the thing is in controversy with the doc, it must be classified as a bug. IMHO there is no place for discussion here.

    Of course when we try to explain WHY the error is happen, we can give the full freedom to our imagination :-).

    Taking that into the account:
    According to p 5. of I., "any" is an abstract type, so any time you deal with variable of "any" type, you actually deal with the variable of any non-abstract type (5.2. of I. - "no value is directly of type any").

    And the value has it's type from the moment it is evaluated, and as we know from p 1.3 of I., function expressions are evaluated eagerly (because they are not "list or record member expressions").

    There is term "compatible" in the p 5. of I., and it is about " all values that conform to X also conform to Y", so no any word about any conversion here. I.e. if variable is of "custom record" type, it's _conforms_ only to "record" or "any" type, not anything else. And thus this means, that it can be passed as a parameter of record type just because it is compatible with record. But there is no any words about any kind of conversion of type of a value.

    So my question to Colin is - where you read anything about "implicit type conversion" in M?

    I don't remember anything about that, moreover - I'm sure that there is not any sign of non-declared implicit conversion of any typed arguments of the functions in M. And that's due to the very obvious thing - what would be a reason to declare the type of a parameter, if M would implicitly converted a value, passed as the parameter, from any other type to a type of argument? Type checking is not for a programmer who doesn't want to use explicit type conversion, but for finding errors of coding on compilation stage!

    So your phrase "again exactly what we would expect from implicit conversion." is not based on any documentation, or please could you be so kind to cite the documentation explicitly.

    And if we do not have anything regarding that in the doc, we definitely have a bug here.

    Summarizing the logic behind classifying the thing as an error:
    1. There is not "implicit conversion." in M, while there is checking for compatibility, which is clearly described in the doc.

    2. You can pass a variable of "any" type as a parameter of record type ONLY if the variable is COMPATIBLE with record type at the moment of it's evaluation, which happens before the evaluation of function body (eager evaluation of parameters). And it is COMPATIBLE ONLY if it is of "custom record" type, but in my example it is of "custom table" type.

    3. According to 6.4.2 of I. we have
    - [] syntax for "selecting" value of a field, which obviously produce a variable of type of the field, and
    - [[]] syntax for "projecting" record or table to a new one, which is of type record or table accordingly.
    So [] under any circumstances cannot provide a record as it's result, until the field value is a record. And in my example it is of type table.

    4. Evaluating value of the field (due to using [] syntax) must take place at the moment of passing the parameter value to "each" function, not at the moment of Record.Field call.

    5. And the value of the field is really not of record type, because it cannot be passed as parameter of record type to "each" function - we see the error if we try to do that.

    So no any logic would let us expect that Record.Field in my example would not end up with the error.

    As for logic which could explain WHY the error takes place, I think that's definitely due to error in Lazy evaluation algorithm. While passing parameter to the "each" expression, PQ still operates with a record of the OuterTable (top-level table) and gets a field value only inside of Record.Field operator. 

    That's why this code works:
    List.Transform({InnerTable},each Record.Field(_,"A"))

    It doesn't have to do anything with the each parameter, while in my case it has to get a field value from record.

    Sunday, July 21, 2019 11:15 PM
  • To me, the entire discussion is pointless, including my own unhelpful contribution. The reason is that if you are explicit in your coding (which is good programming practice in any language), you will never encounter the issues raised in this thread. The proper way to code the expression would be

    List.Transform(OuterTable[B], (i) => List.Transform(Table.ToRecords(i), (j) => Record.Field(j,"A")))

    where converting to a list of records is explicit, prior to applying the Record.Field function. Predictably is 100% every time.

    Monday, July 22, 2019 1:34 AM
  • Andrey, I think it's fair to say that this is a bug.

    When the user query contains a predicate or transformation which is applied to a list or table, we try to normalize it so that the folding code doesn't have to handle as many variations. Both Record.Field and Table.Column get normalized into the field access operator. This means that List.Transform(list, each Record.Field(_, "A")) and List.Transform(list, each Table.Column(_, "A")) both get transformed into List.Transform(list, each [A]). In your case, of course, no folding happens -- but now we have a different expression. The new expression is recompiled and then invoked without regard for the original one. Field access is a primitive that works against both records and tables, so you get a result instead of an error.

    The root cause for this is probably that the table type is a relatively late addition to the product. Originally tables were just lists of records, but this approach turned out to have some drawbacks.

    Monday, July 22, 2019 3:53 PM
  • Well, Colin, waterfall approach is even better, and assembler coding provides faster code :-).

    Anyway, as for me, the discussion was very helpful and forced me to re-think a number of things, thanks both of you guys.

    Meanwhile, it would be great to have some feedback from PBI team regarding their opinion about the problem.

    PQ is a great tool, but it would be great to clean up it from some errors :-). It seems for me, that PBID is much more buggy than Excel now...



    Monday, July 22, 2019 3:58 PM
  • The root cause for this is probably that the table type is a relatively late addition to the product. Originally tables were just lists of records, but this approach turned out to have some drawbacks.

    How late an addition would this have been? The library spec. has been referring to table types since 2013. How could you have functions specific to tables without a table type? (like all other types which have functions specific to their type).

    Monday, July 22, 2019 7:44 PM
  • It would have been changed in 2012. I don't think there was ever a public version without a table type.
    Monday, July 22, 2019 7:53 PM
  • It would have been changed in 2012. I don't think there was ever a public version without a table type.
    Ah, thanks!
    Monday, July 22, 2019 8:53 PM