Small Excel File, Large PBI File

[jcarville]

Hi all,

I'm completely stumped with a simple data model that I have loaded into PBI.

I have an Excel file containing 3 tables totalling 10.6KB:

• Campaign (3 rows)
• Customer (4 rows)
• Medicine (3 rows)

When I load this into PBI the PBI file size is 25.8MB and I cannot see how this is.

Can anyone help me figure out why the file size is so large? The link to the Excel file is here: Excel File

Thanks,

Jake

[chethan]

@magsod after cleaning the date range even the same file size.

@jcarville  because your data is not in the correct formate what PowerBI required as simple as that. if the date is required for calculation then formate to the date type else remove it.

Regards,

Chetan K 

[magsod · Accepted solution]

I looked at the sample data and it clearly contains a date range (until 9999/12/31) that causes the internal date dimensions to be extremely large.

I turned off the option for Auto Date/Time and saved the file and it reduced the size to 115kB ! 

If you don't need the built in time intelligence functions I suggest you turn the option off. It will reduce the size!

Br,

Magnus  

[jcarville]

Hi Magnus,

Thanks for this, I wasn't aware of the option to turn off the auto date/time function. I may remove the 9999/12/31 date also!!

Regards,

Jake

[chethan]

 @jcarville,

Even there is the same issue with my power bi too, however, I have fixed the metadata I mean changing the record type. I have fixed the date column to date formate & DateTime to Date& time formate 

you can find the attached link here

Regards,

Chetan K

[jcarville]

No luck as of yet @chethan in lowering the file size. I have never come across this before using PBI.

If anyone else has any thoughts it would be much appreciated.

[jcarville]

Just bumping this query to see if anyone else can help.

[magsod]

Do you have dates in some of your tables and the range of dates is very large (e.g 1800-01-01 - 2999-01-01 etc.) ?

If this is the case, then the hidden calendar tables that Power BI creates for you will contain many millions of rows wich can cause the file size to be quite big.

Try and turn of the setting for Auto Date/Time in options and save the file to see if this affects the size of the file.

Br,

Magnus  

[chethan]

@jcarville Am unable to download the file

please share the downloadable link

[jcarville]

Hi @chethan, the dropbox link is Dropbox. Hopefully that is of use.

Jake