Copy columns to a new table using M

[InspiredBI]

Hi All,

I am importing a very wide csv ... around 3,000 columns.

Using this as a base table, I want to select 100 columns and create a new table using the Table.SelectedColumns function.

However, rather than selecting the 100 columns from the already imported table, it is reimporting all 3,000 columns again and throwing away 2,900 columns.

How can I force Power BI to copy from the imported table, rather than its source?

Cheers, Steve

[edhans · Accepted solution]

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.

[InspiredBI]

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.

[v-lid-msft]

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,

[InspiredBI]

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

[Jimmy801]

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

[Miralon]

Hi @InspiredBI ,

Are you using PowerBI Desktop or Power Query from Excel?

If you use Power BI Desktop, use DAX function SELECTCOLUMNS with your imported table as source.

If you use Excel Power query,  you can use Blank Query-> Excel.CurrentWorkbook() to expose imported table as source for your new query. 

Hope this helps.

Feel free to request more details if needed.

Sincerely,

Mira Aciu