Forum Discussion
Copy columns to a new table using M
If by base table you mean the query that is importing from the file, and the "already imported table" is a reference to the base table, you cannot tell PQ to just use the smaller 100 column table, because that references the 3,000 column table.
If this were a relational database, Power Query would fold the query back to the server and tell it to only bring back the 100 columns you want. But with any kind of Excel file, text file, SharePoint list, or other non-DB data source, Power Query will always do every step. It doesn't know what 100 columns you want until it brings in the 3,000 first, then it knows which 2,900 to toss.
There is no way around this for text files. Power Query repeats every single step every single refresh.
Hi InspiredBI ,
When use Power Query Editor, Every step are based on the previous step, such as if you reference a query that contain all the columns then select some columns, the select step will based on the previous step. But if the data source is Sql or other database, we can use sql statement to force select some column from datasource, but we cannot use it with a csv file.
If you need to create many queries based on different columns of your csv files, we suggest use the Table.Buffer to keep the origin table in memory to optimize it.
Best regards,
Thank you for your suggestion, but unfortunately, it was not successful.
I added the Table.Buffer to the end of the base table query.
Instead of loading the 40Mb file three times in one pass it loads it three times in three passes.
i.e. 120Mb in both cases... 😕
Hello InspiredBI
the doesn't exist stored data from a Power Query. So whenever you are updating the query, it pulls the data from the CSV-file. So it has the read the whole content of it. Try to put the Table.Buffer arounde your second step, not at the end.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
