none
Table.ColumnsOfType...{Any.Type} / {type any} RRS feed

  • Question

  • Hi

    let
        Source = #table(type table[A=Any.Type,B=Number.Type,C=Text.Type,D=Int64.Type,E=Number.Type,F=Any.Type],
            {{"1a",.5,"abc",10,.75,"zzz"}}
        ),
        ColOfTypeAny = Table.ColumnsOfType(Source, {Any.Type}) /*   or {type any}   */
    in
        ColOfTypeAny /*   => {A,B,C,D,E,F}   */

    Is this really expected ({A,B,C,D,E,F} vs {A,F})? If so, I would highly appreciate some clarifications - Thanks
    Not looking for a solution - know how to get 
    {A,F} in this context


    Tuesday, November 12, 2019 1:54 PM

Answers

  • Hi Lz. Here's a function you can use, courtesy of Curt from our team:

    (table as table) as list =>

    let

        columnsInfo = Record.ToTable(Type.RecordFields(Type.TableRow(Value.Type(table))))

    in

        Table.SelectRows(columnsInfo, each Type.NonNullable([Value][Type]) = type anynonnull)[Name]

    The above code is very similar to the actual implementation of Table.ColumnsOfType, except it uses Type.NonNullable and exact equality to look for the matches instead of Type.Is.

    Ehren


    Wednesday, November 20, 2019 9:10 PM
    Owner

All replies

  • Hi,

    I guess, any in this case means not only type any, but any (all) types. You may use this code (if there are other types in your dataset, second list should be extended, of course):

    let
        Source = #table(type table[A=Any.Type,B=Number.Type,C=Text.Type,D=Int64.Type,E=Number.Type,F=Any.Type],
            {{"1a",.5,"abc",10,.75,"zzz"}}
        ),
        ColOfTypeAny = List.Difference(Table.ColumnsOfType(Source, {type any}), 
                                                           Table.ColumnsOfType(Source, {type number, type text}))
    in
        ColOfTypeAny
    Wednesday, November 20, 2019 4:35 PM
  • Hi Lz. Here's a function you can use, courtesy of Curt from our team:

    (table as table) as list =>

    let

        columnsInfo = Record.ToTable(Type.RecordFields(Type.TableRow(Value.Type(table))))

    in

        Table.SelectRows(columnsInfo, each Type.NonNullable([Value][Type]) = type anynonnull)[Name]

    The above code is very similar to the actual implementation of Table.ColumnsOfType, except it uses Type.NonNullable and exact equality to look for the matches instead of Type.Is.

    Ehren


    Wednesday, November 20, 2019 9:10 PM
    Owner
  • Hi Ehren (& Curt)

    I was using something less "scientific" (Table.Schema) and although this has worked so far there are probably other scenario where this won't. Curt's function is really appreciated - Thank Yous 

    Thursday, November 21, 2019 7:12 AM