Forum Discussion
Expression Error: Column not found in table
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.
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.
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.
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.
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.
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.
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).