locked
Error - loose typing of column RRS feed

  • Question

  • In this code type of column "dd" is set up from a value. And it is loosed, if I delete a column with the value:

        Query1 = #table({"w"}, {{#table(type table [r = number], {{1}})}}),
        Custom3 = List.First(List.Skip(Table.Column(Query1, "w"), each _ = null)),
        Custom1 = Table.RemoveColumns(Table.AddColumn(Query1, "dd", each [w], Value.Type(Custom3)), {"w"}),
        #"Expanded dd" = Table.ExpandTableColumn(Custom1, "dd", {"r"}, {"r"})

    I.e. in #"Expanded dd" I see that type of "r" is any, and in Custom1 type of dd is any.

    I suppose that is an error - the table which is result of Table.AddColumn is evaluated already because it is passed as parameter to Table.RemoveColumns.

    Interesting thing - if I do Table.RemoveColumns like in the code - typing icon of dd changes to type any, but if I do Table.RemoveColumns as separate step, icon is still table, but anyway typing of dd is loosed.

    Both the bufferings don't help also. And frankly I don't see workarounds (UPDATED - there is - do artificial change of the value).

    So I loose ascribing typing in case I call the function as a value for a column and get non-scalar value.

    I cannot use ascribed types as function return type, and Table.TransformColumnTypes doesn't understand non-scalar types (so even non-scalar primitive types like non ascribed record are not allowed). Isn't the latter is strange, by the way?

    And it seems that Table.TransformColumns just ignore the 3-d element of a transformOperation [UPDATED], in case value itself is not changed, which is not logical IMHO.

    So the workaround is: using Table.TransformColumns, in "each"  function replace type of the field value to it's own.



    Friday, August 2, 2019 4:06 PM

Answers

  • You also talk about something else at the end of your post: " Table.TransformColumns just ignore the 3-d element of transformation list, in case value itself is not changed, which is not logical IMHO." 

    There is actually a good reason for this. 

    The third parameter of Table.TransformColumns is defaultTransformation. This takes a function and applies this function to all of the columns not mentioned in the second parameter, (and also uses the return type of that function to change it's column types). Sometimes you would want to apply a transformation to all columns except some other columns. E.g.

    let
    
    table = someTable, //Has 20 columns
    
    columnsToIgnore = {"Date","Name"},
    
    transformationOnRestOfColumns = Text.Upper,
    
    transformColumns = Table.TransformColumns(table, List.Transform(columnsToIgnore, {_,each _}), transformationOnRestOfColumns)
    
    in
    
    transformColumns


    The crux of the argument: In this example "Date" and "Name" do not have their columns type changed to type any even though the function return type of each _ is type any. This makes each _ very useful if you wish to use the defaultTransformation parameter.   

     

    I should also mention that (X)=>X works the same way as each _ if anyone was wondering if each _ was special in any way.

    If you want to ascribe types using Table.TransformColumns without transforming the values you can use each {_}{0} which is mentioned in a post here: https://social.technet.microsoft.com/Forums/en-US/636e9b44-6820-4ff2-ab60-5dd6a5307bd2/type-conversion-mysteries

    by Marcel Beugelsdijk. Probably the best option for the best performance  


    Sunday, August 4, 2019 12:44 AM
  • Hi Andrey,

    Yes I understood that, actually my post is the one that is confusing. So "The third parameter of Table.TransformColumns" is related to the 3rd sub parameter of the 2nd parameter in a complicated way.  Basically "each _" tells Power Query to do nothing to that column (even if you provide the 3rd sub-parameter). Normally it will change the column type to the 3rd sub-parameter and if you don't provide the 3rd sub-parameter it will use the function return type of transformation function instead (2nd sub-parameter of 2nd parameter). But instead  "each _" say's ignore this column (this at first seem very useless). But it is useful if you use defaultTransformation (3rd parameter) and you wish to ignore certain columns. 

    You say "But the value itself must be changed by the 2-nd element of the list in anyway in order this 3-d element works". The latter part of my previous post deals with this.

    Monday, August 5, 2019 10:45 AM

All replies

  • You also talk about something else at the end of your post: " Table.TransformColumns just ignore the 3-d element of transformation list, in case value itself is not changed, which is not logical IMHO." 

    There is actually a good reason for this. 

    The third parameter of Table.TransformColumns is defaultTransformation. This takes a function and applies this function to all of the columns not mentioned in the second parameter, (and also uses the return type of that function to change it's column types). Sometimes you would want to apply a transformation to all columns except some other columns. E.g.

    let
    
    table = someTable, //Has 20 columns
    
    columnsToIgnore = {"Date","Name"},
    
    transformationOnRestOfColumns = Text.Upper,
    
    transformColumns = Table.TransformColumns(table, List.Transform(columnsToIgnore, {_,each _}), transformationOnRestOfColumns)
    
    in
    
    transformColumns


    The crux of the argument: In this example "Date" and "Name" do not have their columns type changed to type any even though the function return type of each _ is type any. This makes each _ very useful if you wish to use the defaultTransformation parameter.   

     

    I should also mention that (X)=>X works the same way as each _ if anyone was wondering if each _ was special in any way.

    If you want to ascribe types using Table.TransformColumns without transforming the values you can use each {_}{0} which is mentioned in a post here: https://social.technet.microsoft.com/Forums/en-US/636e9b44-6820-4ff2-ab60-5dd6a5307bd2/type-conversion-mysteries

    by Marcel Beugelsdijk. Probably the best option for the best performance  


    Sunday, August 4, 2019 12:44 AM
  • Hi Cam!

    Thanks for you replay, and sorry for not be clear enough in my question.

    I meant the 3-d element of a transformOperation, not the 3-d parameter of Table.TransformColumns. This 3-d element is the type of the transformed column. I do not see it's description in the function's documentation, but I tried to use it supposing it should be such a feature, and it works, at least in case of ascribed table type. But in order to make this 3-d element work, the value itself must be changed in any way (not just "each _") by the 2-nd element of the list of the transformOperation.

    I should check ascribing for function return value via Value.ReplaceType, thanks for the clue.

    Just in case - ascribed return type of the M library functions is the great source of ascribed typing.

    Though it would be great to have appropriate ascribed Record type for the Attributes column of Folder.Conents.


    Monday, August 5, 2019 9:44 AM
  • Hi Andrey,

    Yes I understood that, actually my post is the one that is confusing. So "The third parameter of Table.TransformColumns" is related to the 3rd sub parameter of the 2nd parameter in a complicated way.  Basically "each _" tells Power Query to do nothing to that column (even if you provide the 3rd sub-parameter). Normally it will change the column type to the 3rd sub-parameter and if you don't provide the 3rd sub-parameter it will use the function return type of transformation function instead (2nd sub-parameter of 2nd parameter). But instead  "each _" say's ignore this column (this at first seem very useless). But it is useful if you use defaultTransformation (3rd parameter) and you wish to ignore certain columns. 

    You say "But the value itself must be changed by the 2-nd element of the list in anyway in order this 3-d element works". The latter part of my previous post deals with this.

    Monday, August 5, 2019 10:45 AM
  • Finally read the post :-), {_}{0} seems to be the smallest overhead, thanks for this!
    Monday, August 5, 2019 7:57 PM