Power query: not all content is allowed to run

Hi there, would be very grateful for support as can’t find anything on Google to help!

  1. I created queries on Excel Power Query to analyse my data. Afterwards, I added a couple of questions to the original survey. The query then gave an error message that certain columns (the metadata columns; submitted by, index_, etc) could not be found. I did a manual download of the survey data and could see the columns there, just in a later order as they’d been bumped forward by the additional questions. Why does this happen - I thought Power Query identified columns by their title, not by their order?

  2. I managed to sort this problem by going into the advanced query editor and deleting any reference to columns which the queries couldn’t find. The queries loaded fine after that, but when I closed that window to get back to excel, I then got a message saying ‘trusted document settings have changed’, not all content in this file is allowed to run’. I’ve tried changing the source data and putting in the updated synchronous export link, but that hasn’t worked. Does anyone know why this message is there + what I can do?

Thank you

@abaneke, maybe this Microsoft blog could help you solve your issue.

Hi Kal, thanks for sharing but the problem that blog addresses is where columns have been re-named, which in my survey they haven’t been. Power Query should still be able to recognise them, which is what I’m confused about

For other users : I’ve solved this! The query doesn’t automatically extend the number of columns it can refer to if you add questions, so if your survey started with 10 questions, the number of columns in your query will remain as 10.

You need to go into the source step of your query, then find the code reference to ‘Columns = X’, and whatever number X is, replace it with the correct number so that the query registers all columns in the dataset.

2 Likes

@abaneke, :clap: :heart: :partying_face: