cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rickylee Member
Member

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Seward12533 New Contributor
New Contributor

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

Here you go...

 

EDIT QUERIES

 

snip_20180811225128.png

 

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"

snip_20180811225426.png

 

 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

 

snip_20180811225647.png

 

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.

View solution in original post

5 REPLIES 5
Seward12533 New Contributor
New Contributor

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

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.
rickylee Member
Member

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

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 New Contributor
New Contributor

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

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 New Contributor
New Contributor

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

Here you go...

 

EDIT QUERIES

 

snip_20180811225128.png

 

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"

snip_20180811225426.png

 

 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

 

snip_20180811225647.png

 

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.

View solution in original post

KevinFromWpg Frequent Visitor
Frequent Visitor

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

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".

Query Steps.JPG

 

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.

 

RenameCols.JPG

 

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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)