Remove empty columns - filesize issue

[thibbos]

Hi everyone,

I need to clean up a powerbi that consists of many different source files (excel/csv).

Some of these source files contains many columns and I am trying to remove the empty ones automatically, through formula i found on the forum  Remove Empty Columns .
My current excel file is 15MB big,

but when applying above formula to the query, the preview runs and says the file is above 1.5GB.

This is why my report is very slow, every time it needs updating .... it runs for 20 minutes only for this query

My excel file contains 266 columns (of which around 90 are actually empty.) for 35K rows of string data mostly.

Am I missing something here? Mostly when using powerbi the powerbi is smaller than the excel.

Should I apply the formula right after the "source" step? 
I really have no clue and it would help me with all other source file i need to clean up.

Thanks a bunch
Thibbos

[HotChilli]

I haven't tried the formula but it's building a list for each column and then testing if all values are null so if it's causing a problem I wouldn't bother.

----

If you've got 255 columns you might concentrate on removing a good chunk of them especially those with high cardinality.  I can't believe you'll be interested in 255 columns.

Also disable Auto Date/Time option if you've got a few datetime fields.  Build your own Calendar table.

Good luck

[thibbos]

Hi @HotChilli ,

Exactly why i need this formula to work better, removing empty columns is only the first step. The source system creates an ID (string of 15 characters) for almost each field, so these are next. But it still needs to be sufficiently efficient:

- Date/Time is disabled, always .. i have own calendar table

There is no issue with formula, it works but building up to over 1,5gb while my file is only 15mega ... you can imagine how long that takes.

Thanks for suggestion

[thibbos]

Could it be because the "formula" is looping through all columns to determine if it's empty or not? Using the file size multiple times?
Of course one of the solutions might be to remove them manually, but the goal is to have them appear should there be any input at one point in time.

[thibbos]

By the way, I finally opted for manual removal anyway .... the formula took way too much time and it was in the end more efficient manually