Expression Error: Column not found in table

[freshguy222]

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!

[MarcelBeug]

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]

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.

[freshguy222]

Hello everyone,

I am currently trying to import my PowerPivot DataModel into PowerBI Desktop. I manage to import the data and create visuals but I cannot refresh any existing tables. I get the following error message:

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

There are two columns in this exemplary table (id, supplier). When I check the advanced editor it seems that the error appears during the 'Rename Columns' step (I dont want to convert data types or change column names anyway). The columns given here are exactly the same as in sourceDB and PowerPivot.

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"

Any help would be highly appreciated.

[freshguy222]

Hello everyone,

I am trying to copy my PowerPivot Data model into PowerBI Desktop. I can import the complete mode (tables, queries, measures) but upon the first refresh of a table (all the tables fail) in my Database (MySQL) I get the following error message:

I imported the Excel file via Import of 'Excel Workbook Contents':

The migration succeeds.

However when I want to refresh one of the tables I get the following error message:

In PowerPivot the query for this table looks as simple as this and refreshes successfully.

SELECT 

id,
CASE
WHEN name = 'F2F PS Cartus' THEN 'Cartus' ELSE name
END AS 'Supplier'

FROM supplier

The query editor shows these steps:

The Advanced Editor shows the following code:

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"

I understand that there must be a navigation step missing that connects to the data source but would assume that this is done during the migration.

This is the connection string to MYSQL DB (same as in PowerPivot)

dsn=xxx;initial catalog=xxx;persist security info=False;provider=MSDASQL.1

I use Excel 2016.

Any help would be greatly appreciated.

[v-shex-msft]

Hi @freshguy222,

Can you share us a pbix file with some sample data to test? It will be help for troubleshooting.

Regards,

Xiaoxin Sheng

[freshguy222]

Unfortunately cannot share the file. I have decided to build my DataModel from scratch in PowerBI which is good enough for my purposes. Maybe it can indeed not be done with that kind of setup. Thanks for the help!

[MarcelBeug]

Again you didn't create a fresh new topic, so this may remain under the radar for others to help you.

This is what I see in my Notfications:

Anyhow, if I see it correctly, in Excel you import your data directly into Power Pivot, so not via Power Query.

I think that's the primary cause of your issue,

So the big question is: how to move an Excel Power Pivot datamodel, that doesn't use Power Query, to Power BI Desktop.

Unfortunately I don't have the answer and I don't know if it can be done at all.

If you don't get any usefull reactions shortly: try and create a real fresh new topic (e.g. next Monday).

[MarcelBeug]

I'm not sure what you did, but this is not a new topic and will probably remain unnoticed.

You'd better create a fresh new topic.

In your description you can include a link to this topic (or not if it is not required).

If I take a closer look to your issue, I would expect that you have multiple queries (1 for each table)?

Are they all wrong?

There should be a navigation step after the first in which you connect to the data source.

I my view, your code could never have worked this way. Mayb it was adjusted after the first run?

It looks like you are not connecting to PowerPivot, but you want to copy your queries from Power Pivot to Power BI?

Then I wonder how your query looks like in Power Pivot: does it work over there?

As you can see, there are a lot of questions left, so I would advice to create a topic with much more information and for instance some screenshots.

[MarcelBeug]

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.