Expand excel table but limit columns

[Anonymous]

looking for way to improve the performance of a PBI report that brings in an excel table by limiting the columns

at this stage when i create the steps to find the file and excel worksheet, i need to expand out the specific table to bring in the columns into the report

but when i do that, it brings in all columns and rows even blanks and severly slows down the whole file
i am unsure why it does this and not just bring in just the columns and rows with data.
is there a way to limit the expansion of the table upon the step or do i need to remove columns as a subsequent step

the report needs to bring in the first 400 columns of a excel table and roughly 250 rows

any help would be appreciated

[Ashish_Mathur · Accepted solution]

Hi,

Why is it fetching columns which are blanks into the Table?  My guess is that there is formatting lying in the cells which look like blank cells to you..  Please open the source Excel file and delete the empty rows and columns.  Save the file and close the file.

[Ashish_Mathur · Accepted solution]

Hi,

Why is it fetching columns which are blanks into the Table?  My guess is that there is formatting lying in the cells which look like blank cells to you..  Please open the source Excel file and delete the empty rows and columns.  Save the file and close the file.

[Anonymous · Accepted solution]

@Ashish_Mathur thanks. i will try it again. that was one of the first things i did prior to putting the thread up

[PhilipTreacy]

@Anonymous 

Won't you still need to go through 16,000+ columns?

Will the source file be regenerated thereby creating all 16,000+ columns again?

Download sample PBIX with code shown below

You can filter out all empty columns using Power Query by:

1. Unpivoting the columns

2. Filtering out the blank rows (these were the empty columns before unpivoting)

3. Pivoting

You could also write a custom function to select only the columns that are not empty.  I'll write this if you don't want to use the Unpivot/Pivot approach.

Regards

Phil

Phil

[Anonymous]

@Ashish_Mathur in the end i re-did the whole query and that solved the problem
but on comparison with the new query, all steps are the same but behave differently. anyhow looking good
thanks

[Ashish_Mathur]

Good to know that.

[PhilipTreacy]

Hi @Anonymous 

I can't really think of an easy way to do the column selection dynamically.

When you click on the double headed arrow to expand the table, Power Query is using the Table.ExpandTableColumn function and this requires that the columns be named.

So if you can get a list of the column names you want then you can feed these into the function, and avoid lots of clicking.

Alternatively, if you had some way to add some common prefix (for example) to the columns you wanted, you could use the Search ability in the Expand Columns dialog to select only these.

In this example I've renamed 2 of my columns to start with xxx_ and then searched for only these to expand them.

Not sure how practical this is for you, but these are ways to do it.

Regards

Phil

[Anonymous]

@PhilipTreacy 
thanks for reply
yeah doesnt look like that improves the performance of file as it still looks like it searches through

ill keep tweaking and see if there is something i am missing
thanks

[PhilipTreacy]

Hi @Anonymous 

When you click on the double headed arrow to expand the table, you get the choice of what columns to expand.  Can't you use that to limit the columns that appear?

Regards

Phil

[Anonymous]

@PhilipTreacy 
thanks, yes that could do it
though the list of columns goes upwards to 16380, is there a way to dynamically do that without having to click on 15800 select boxes?