Power BI Desktop loading 100 MB out of a 70 KB File

[JoshEnglish]

One of the files I import and merge into my database is only 70 KB, but every time I refresh the data Power BI Desktop takes a long time (20-30 minutes) and reports that it's loading well over 100MB of data from this file.

Any suggestions on how I can get this file under control?

[TomMartens]

Hey,

this is not a simple question, because this depends on how Power BI works and cashes data from data sources. I assume that the file is referenced by one or more queries in your subsequent data processing.

Without having more details your question can't be answered.

The most current write up is here:

https://blog.crossjoin.co.uk/2016/11/20/referenced-queries-and-caching-in-power-bi-and-power-query/

Please be prepared for some mindboggling reading.

Regards,

Tom

[rafaelmpsantos]

Maybe this file is in a loop event in powerquery. Can you share you M code here?

[JoshEnglish]

The file has three tables that feed into other reports which are part of a final query.

Table 1 is merged into the final query

Table 2 is merged into the final query

Table 3 is merged with a third query which is the source of a fourth query which is the basis of the final query.

I wonder if it's one of the other queries and PBI is only reporting the name of the first file.

[v-yuta-msft]

Hi JoshEnglish,

How does your query of table1 , table2 and merge operation look like? Are there table scanning in your query?

Regards,

Jimmy Tao

[JoshEnglish]

All three have the same format for the inital query:

let
    Source = Excel.Workbook(File.Contents("C:... THE XLSX FILE"), null, true),
    Table = Source{[Item="TABLE_NAME",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table,{ DEFAULT TYPE CHANGES  })
in
    #"Changed Type"

One of them has an additional replace value (null for today's date). I'm not sure what you mean by Table Scanning, though.

[v-yuta-msft]

Hi JoshEnglish,

Have you solved issue by now? If you have, could you kindly mark one answer to finish this thread?

Regards,

Jimmy Tao

[v-yuta-msft]

Hi JoshEnglish,

Your operation seems not so complex. How about the CPU and ram of your computer?

Regards,

Jimmy Tao