Forum Discussion
Helper IAmending a Query to recognize an additional column in an Excel data source
Hi, Grateful for a steer. My data source is an Excel spreadsheet. The data provider added an additional column and I added "{"Column41", type any}})," in the Advanced editor. However, when I refreshed I got the "Expression.Error: The column 'Column41' of the table wasn't found." message.
I ried deleting ""Column41", type any," but this then raised another error because I had performed a query on the new column.
Is thereanother way to add the new column?
Here is the relevant section of the query -
let
Source = Excel.Workbook(File.Contents("xxxxxxxxxxxxxxxxxxxx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data_Sheet,{{"Column1", type any}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type any}, {"Column12", type any}, {"Column13", type text}, {"Column14", type any}, {"Column15", type any}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type text}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type text}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type text}, {"Column32", type any}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type any}}),
Regards,
Mila
The error points to the update: it does not appear to pick up the new column.
You don't need to add code to change the column type in order to pick it up, it should happen automatically.
I've heard of this, but I've never seen it.
People used to say "you have to add the column to the end in Excel, not the middle" or "You have to update the first step in the query - by clicking on the gear in the query and update steps" - I've never seen this either
Therefore, if I were you, I would double check that the query is pointing to the correct file (not the previous version of it)
Test an experiment by creating your own Excel file and connect to it using powerbi, then add a column to Excel and update the powerbi query. See if they pick it up.
There is also another method to convert the data into a worksheet into a table and connect powerbi to the table - you should be able to google it.
Good luck.
That worked, Thank you! 🙂
2 Replies
- HotChilli
Community Champion
The error points to the update: it does not appear to pick up the new column.
You don't need to add code to change the column type in order to pick it up, it should happen automatically.
I've heard of this, but I've never seen it.
People used to say "you have to add the column to the end in Excel, not the middle" or "You have to update the first step in the query - by clicking on the gear in the query and update steps" - I've never seen this either
Therefore, if I were you, I would double check that the query is pointing to the correct file (not the previous version of it)
Test an experiment by creating your own Excel file and connect to it using powerbi, then add a column to Excel and update the powerbi query. See if they pick it up.
There is also another method to convert the data into a worksheet into a table and connect powerbi to the table - you should be able to google it.
Good luck.
MilagrosThat worked, Thank you! 🙂