Forum Discussion

freshguy222's avatar
freshguy222
Frequent Visitor
9 years ago

Expression Error: Column not found in table

Hello everyone,

 

I am connecting PowerBI Desktop to a data model in PowerPivot and get the following error message during refresh attempt:

Expression.Error: Column 'id' could not be found in table.

 

There are two columns in this exemplary table (id, supplier).

 

let
    Source = Odbc.DataSource("Provider=MSDASQL.1;Persist Security Info=False;Initial Catalog=learnship;DSN=learnship"),
    #"Renamed Columns" = Table.RenameColumns(Source, {{"id", "id"}, {"Supplier", "Supplier"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"id", Int64.Type}, {"Supplier", type text}})
in
    #"Changed Type"

In the pane on the right it says that the error happens during 'Renamed Columns', however I dont want to change Column name or data type. The import succeeds but any subsequent refresh fails.

Any help would be highly appreciated, thanks!

 

 

 

9 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    My best guess would be that it is a matter of case sensitivity, e.g. the column is named "Id"  instead of "id".

     

    If you want to avoid errors, you can add a 3rd argument MissingField.Ignore (or just 1) to Table.RenameColumns.

     

    So you might adjust that step to

    #"Renamed Columns" = Table.RenameColumns(Source, {{"id", "id"}, {"Id", "id"},{"Supplier", "Supplier"}, MissingField.Ignore}),

     

    Alternatively, you can use Table.TransformColumnNames e.g. to make all column names lower case:

     

    #"Renamed Columns" = Table.TransformColumnNames(Source, Text.Lower),

     

    Note: this function is not used in any code that is generated from the available menu options, so it must be entered in the formula bar or in the advanced editor.

    • freshguy222's avatar
      freshguy222
      Frequent Visitor
      9 years ago

      Unfortunately that does not work, still receive the same error message.

      Also, if I remove both steps (Renamed Columns Changed Type) and leave only 'Source' all my columns disappear and I get 235 rows of table information (Catalog, Description, Item etc.), one row for each table in my source DB (MySQL).

       

      I would like PowerBI to make asbolutely no changes to my data model actually.

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        I have no experience with connecting to Power Pivot, but I know you can import Excel workbooks into Power BI.

         

        Or search for information available, e.g. in this topic.

         

        It looks like the subject ("Expression Error: Column not found in table") doesn't cover what you actually want to achieve, so if you can't find sufficient information, then my advice would be to raise a new topic with a title like "How to copy Power Pivot to Power BI?" or something similar.