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

Copy columns to a new table using M

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
Highlighted
Frequent Visitor

Re: Copy columns to a new table using M

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

Highlighted
Community Support
Community Support

Re: Copy columns to a new table using M

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
New Member

Re: Copy columns to a new table using M

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

Highlighted
Super User II
Super User II

Re: Copy columns to a new table using M

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

Highlighted
Super User III
Super User III

Re: 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
New Member

Re: Copy columns to a new table using M

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.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors