After 'applying' to remove a column in Edit Queries, the column isn't removed properly.

[rickylee]

Hello, 

My current database table has column1 and it is now being removed in the  the datasource. So I need to remove column 1 in edit queries and apply. After doing so, I still cannot retrieve the data from the datasource because it complains that I have the extra column1. 

There is a possible soution in this post but it kills everything. It toggles 'enable load' in the table. This kills all the measures, calculated columns and relationships.

https://community.powerbi.com/t5/Desktop/After-applying-new-column-arrangement-in-Edit-Queries-Data-...

Now, I have a table with 100 measures already. Applying the above method can be time costly. Are there any better way to fix this?

Thanks,
Ricky

[rickylee]

Hi @Seward12533,

Could you give a screenshot where the process is about? I am not comfortable with M language but could learn from it.

Thanks,
Ricky

[Seward12533]

Eventually but not near a computer.

While written for excel once in power query (edit query from PBI) it’s the same.

https://www.myexcelonline.com/blog/advanced-editor-in-power-query/

[Seward12533 · Accepted solution]

Here you go...

EDIT QUERIES

Then Rebuild new copy of query that works with the new or updated data source and either right click over the query name or select Advanced Editor form the ribbon

Then either right click over the name of the new query and then select "Advanced Editor"

 From the Advanced Editor Panel - Select ALL (CRTRL A) and Copy.  And then open up the Original Query that is no longer workin or you want to replace and then Select All (CTRL-A) and Paste

Save the original query and optionally delete the Temporary Query or set it not to load

Then Close and Apply and then test if it will refresh or not.

[KevinFromWpg]

I ran into the same issue, and the resolution may be easier than re-creating the entire query and copying/pasting the text into the advanced editor.

Simply put, the column I removed was referenced within subsequent steps of the query.  Specifically, I had a step that renamed some columns, including the one that I had removed.  The query editor is apparently not smart enough to go through and remove all references to that column in every step. So, as someone stated above, if you are at all comfortable with the advanced query editor you can just scan through there to see if/where the removed column name shows up, and address it.

My example is here:

In my query, I deselected "Column1" in step A.  But in step B, I had renamed "Column1" to "Column99".

If I open the Advanced Query editor, you can see the last step, #"Renamed Columns"...   and in there it is still trying to rename Column1 to Column99.  So I just simply had to remove {"Table1.Column1","Column99"}, and leave the other 2 columns in there. That solved my problem.

Obviously this is a fairly straight forward example.  But again, if you are OK with opening the advanced editor you can at least look to find where the removed column is still being referenced.  Worst case you can remove the step, and recreate that one step in your current query.  For example I would just remove the "Renamed Column" step and then re-insert it only renaming the other 2 columns.

[Seward12533]

You should be able to edit the query. If your not comfortable with the M language and the query is not too complex just start a new query that duplicates your original query and the right click over the new query and pick advanced editor. Then select all and copy. Then edit the query your data source is using and select all and paste. Save that verify it looks right. Then remove the temporary one or disable load.