none
Table.ColumnsOfType does not work reliably RRS feed

  • Question

  • Table.ColumnsOfType should return columns of the given type but I have noticed that sometimes some columns are simply not returned. Here's an example to reproduce this problem. Tested on Excel 2016 + Access 2016 as well as Access 2013 + Excel 2013 + Power Query Addin (latest version).

    let
        filepath = "database.accdb",
        Quelle = Access.Database(File.Contents(filepath)),
        t1 = Quelle{[Schema="",Item="Table1"]}[Data],
        t2 = Table.TransformColumnTypes(t1, {"Test", type number}),
        t3 = Table.AddColumn(t2, "Test2", each null, type number)
    in
        Table.ColumnsOfType(t3, {type number})

    The example requires an Access database with a table called "Table1" having two columns "ID" (AutoNumber) and "Test" (Short Text).

    Unless I've misunderstood Table.ColumnsOfType the result of this code should be {ID, Test, Test2}. Only {ID, Test2} is returned though. It seems Table.TransformColumnTypes does not change the type in a way ColumnsOfType notices.

    Also of note is that calling ColumnsOfType with (t1, {type text}) returns an empty list instead of {Test} (which is a column of type "Short Text").

    With Access 2013 not even the AutoNumber-Type columns are returned for type number. Not sure what the problem there is though.

    In all of these cases the Power Query Editor correctly identifies and shows the type in the GUI under Datatype: <type>.

    What I'm actually trying to do is set default values for columns that do not have a value (null) as a result of left-joining multiple tables. The code I was trying to use was:

    Table.ReplaceValue(t, null, 0, Replacer.ReplaceValue, Table.ColumnsOfType(t, {type number}))

    Please note I have not tested the above snippet but simply typed it out from memory. The idea should be clear however.

    If there's an easy way to set default values in a table with 40+ columns based on the type that does not involve using Table.ColumnsOfType I'm fine with that too.

    Thank you for the help.

    Friday, January 1, 2016 11:23 AM

Answers

  • Not elegant, but working: Replaces null with 0 in all number columns of table "Source":

    = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue, Table.RemoveRowsWithErrors(Table.AddColumn(Table.FromList(Table.ColumnNames(Source)), "Custom", each let Col=[Column1] in List.Sum(Table.Column(Source, Col))), {"Custom"})[Column1])


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Andrin Meier Sunday, January 3, 2016 2:20 PM
    Saturday, January 2, 2016 1:42 PM
    Moderator

All replies

  • Not elegant, but working: Replaces null with 0 in all number columns of table "Source":

    = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue, Table.RemoveRowsWithErrors(Table.AddColumn(Table.FromList(Table.ColumnNames(Source)), "Custom", each let Col=[Column1] in List.Sum(Table.Column(Source, Col))), {"Custom"})[Column1])


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Andrin Meier Sunday, January 3, 2016 2:20 PM
    Saturday, January 2, 2016 1:42 PM
    Moderator
  • Very creative. I'll give that a try, thank you.
    Sunday, January 3, 2016 2:20 PM
  • It seems Table.TransformColumnTypes does not change the type in a way ColumnsOfType notices

    Came across the same behavior with the following scenario

    let
        Source = #table(5, {{"1a",.5,"abc",10,.75}}),
        ChangedTypes = Table.TransformColumnTypes(Source, {{"Column1",Any.Type},{"Column2",Number.Type},{"Column3",Text.Type},{"Column4",Int64.Type},{"Column5",Number.Type}}),
    
        ColOfTypeNumber = Table.ColumnsOfType(ChangedTypes, {Number.Type}),
            //  ColOfTypeNumber => {} (list is empty)
        ColOfTypeText = Table.ColumnsOfType(ChangedTypes, {Text.Type})
            //  ColOfTypeText => {} (list is empty)
    in
        ColOfTypeText

    Found explaination here => Table.TransformColumnTypes() automatically returns nullable datatypes

    Revised code:

        ColOfTypeNumber = Table.ColumnsOfType(ChangedTypes, {type nullable number}),
        ColOfTypeText = Table.ColumnsOfType(ChangedTypes, {type nullable text})

    For type any/Any.Type see Table.ColumnsOfType...{Any.Type} / {type any}
    • Edited by Lz._ Thursday, November 21, 2019 6:50 AM
    Tuesday, November 12, 2019 1:13 PM