none
'We cannot convert the value 0 to type Function' Power Query error message? RRS feed

  • Question

  • Hello,

    I have a simple, text-based table (no Integers/Dates/etc.) with 3 columns.  I am looking to group 2 of the columns and expand the values of the 3rd column to create additional columns such that each unique grouping is written out to one row instead of multiple rows.

    I start with data that looks like this:

    USERS   GROUPS     AUTL PERMISSIONS
    GAPPACG  EUZAUTL USE
    GAPPACG  EUZEPSAUTL   USE
    GAPPACG  FIDAUTL USE
    GAPPACG  PRIAUTL USE

    I want the expanded data to look like this:

    USERS   GROUPS AUTL1  AUTL2  AUTL3  AUTL4  PERMISSIONS
    GAPPACG EUZAUTL  EUZEPSAUTL  FIDAUTL  PRIAUTL  USE

    Here is the code I'm attempting to use:

    let
        Source = Excel.CurrentWorkbook(){[Name="IMAGrpAUTL_Tbl"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"USERS GROUPS", type text}, {"AUTL", type text}, {"PERMISSIONS", type text}}),
    GroupedRows = Table.Group(ChangedType, {"USERS GROUPS", "PERMISSIONS"}, {{"Table", each _, type table}}),
        TableColumn_AddedIndexColumn = Table.TransformColumns(GroupedRows, {"Table", each Table.AddColumn(_, "Index", 0, 1)}),
    TableColumn_UnpivotedOtherColumns = Table.TransformColumns(TableColumn_AddedIndexColumn, {"Table", each Table.UnpivotOtherColumns(_, {"USERS GROUPS", "PERMISSIONS", "Index"}, "Attribute", "Value")}),
        TableColumn_AddedCustom = Table.TransformColumns(TableColumn_UnpivotedOtherColumns, {"Table", each Table.AddColumn(_, "Attribute2", each if [Index] = 0 then [Attribute] else [Attribute]&Number.ToText([Index] + 1))}),
        TableColumn_RemovedColumns = Table.TransformColumns(TableColumn_AddedCustom, {"Table", each Table.RemoveColumns(_, {"USERS GROUPS", "PERMISSIONS","Index", "Attribute"})}),
        //Following step is added to guarantee that inner table with the most attribute rows is sorted to the top. Otherwise, columns will be lost in the ExpandTable step.
        AddedCustom = Table.AddColumn(TableColumn_RemovedColumns, "Rows", each Table.RowCount([Table])),
        TableColumn_PivotedColumn = Table.TransformColumns(AddedCustom, {"Table", each Table.Pivot(_, List.Distinct(_[Attribute2]), "Attribute2", "Value")}),
        SortedRowsDescending = Table.Sort(TableColumn_PivotedColumn,{{"Rows", Order.Descending}}),
        RemovedAddedColumn = Table.RemoveColumns(SortedRowsDescending,{"Rows"}),
        #"Expanded Table" = Table.ExpandTableColumn(RemovedAddedColumn, "Table", {"AUTL", "AUTL2", "AUTL3", "AUTL4"})
    in
    #"Expanded Table"

    When I run the code the 'TableColumn_AddedIndexColumn = Table.TransformColumns' step produces the following error:

    Expression.Error: We cannot convert the value 0 to type Function.
    Details:
        Value=0
        Type=Type

    The second step in my code transforms all Column Types to Text, and there are no extra spaces to be trimmed.  There are no cell values anywhere in the table that are carrying anything that could be interpreted as a number.  Why am I getting this error?

    Thank you!

    Thursday, September 29, 2016 1:42 PM

Answers

All replies