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.
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.
Thanks for the confirmation.
It would be great to add a feature where Power Query was able to reuse previously calculated interim results.
I will probably have to provision a new database and automate the detection and loading of the new text file.
Work I was hoping to avoid.
I had a similar issue with DAX where the same subquery was being executed 50 times.
PBI was unable to detect and reuse interim results.
In a future release hopefully.
Thanks again.