Power Query - update table to remove columns and pull from new source

[momentumons]

Hello! I feel this this should be simple but i just keep coming across problems..

I have a excel doc that uses Powery Query very simply to update a MASTER TABLE (one tab/table) every day. It pulls in fresh data every day (completely replaces the old data) and then I have millions of other tabs and formulas that point to that data.

I have been using this successfully for YEARS and it has been brilliant.

However, the source file report that I have been using has 47 columns of data and the file size is getting massive (causing issues with scheduling etc). I actually only use 9 of the columns! So I have created a new daily report that has only 9 of the columns. Yay.

How do I update my existing Spreadsheet so the EXISTING TABLE/Power Query points to the new source and only brings in the 9 relevant columns. I don't want to create a new query/tab/table because it breaks allllllll my other formulas.

I have tried removing the columns in the query string and changing the source file but it seems to then create a 'connection only' and I cannot then load it where a table already exists?

I spent HOURS re-doing this yesterday but I feel like it should be a 10 second job. What am i doing wrong?

Thanks!

 

[RoryA]

If you edit the existing query, it will automatically refresh to the same location - i.e. update the existing table.

 

[momentumons]

@RoryA Yes thanks I understand that and that is what I am trying to do. Hopefully somone can walk me through the steps of editing the query. I have tried simplychanging the source to the new source but the query breaks because it's looking for columns that don't exist. I have also tried to remove the columns I don't need and then change the source but this seems to create a new query in a new tab as a connection only... this is where I keep getting stuck. Can anyone help? Sorry - I feel like it should be so simple? I want to keep it clean and not have it ignoring missing fields or anything., It just needs to copy the data from one excel csv file into the new excel....simple, right?

 

[RoryA]

If you edit the existing query, it cannot then load to a new place. It would have to load to wherever the current one does. You will need to alter the Source part, then amend the steps - probably in the second step - to remove any columns you don't want, and make sure any subsequent steps don't refer to columns you removed.