Speed up Power Query Data Model loading in Workbook

[LockeGarmin]

I've got a workbook that I've put about 17 queries and 7 functions into. All of the queries are based off of tables that are contained within the workbook itself, this is not gathering any data outside of the workbook itself. Unfortunately it seems to take about 40 seconds on my 64-bit version of Excel to load and about 2 minutes on another 32-bit version of Excel. My input tables are completely blank or have no more that 10 records so I know it's not the amount of Data. Switching all of the query properties to "Fast Data Load" doesn't appear to help either. Right now my query schema is Input Table A, Input Table B, Table C (Derived From Table A + Table B), and Tables D, E, F, G....(the other 14 tables, all derived from Table C).

Any advice? Thanks!

 

[Matt Allington]

I would generally not advise to load data from within the same workbook. Doing this causes Excel to store a compressed version of he data and also an uncompressed version. Do yourself a favour and split the source and reports into 2 books.

40 seconds to refresh is not a long time, but the change above could make it faster.

I don't know what you mean by 7 functions.

 

[SpillerBD]

32-bit. Burn it !

 

[LockeGarmin]

I would generally not advise to load data from within the same workbook. Doing this causes Excel to store a compressed version of he data and also an uncompressed version. Do yourself a favour and split the source and reports into 2 books.

40 seconds to refresh is not a long time, but the change above could make it faster.

I don't know what you mean by 7 functions.

I totally forgot to thank you for taking the time to answer my question! Thanks! I appreciate the insight on how power query double stores the data. I was really hoping that power query had a special way to access the workbook it resides in to speed things up but that doesn't appear to be the case. The requirements ended up making power query mostly unusable and somewhat hard to maintain so I switched tooling and made a small program that would gather the workbooks and create the output.

By functions I meant the following in the advanced editor:

If I have a table in power query:

let    
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}})
in
    #"Changed Type"

I had been using functions as a clean way to produce a table based on the base table and a parameter or two like the following:

(Column1Value as text) as table =>    
    let
        Source = Table1,
        #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] = Column1Value))
    in
        #"Filtered Rows"

Thanks again for responding. I'm sorry it took this long to hear back from me.

 

[LockeGarmin]

32-bit. Burn it !

Seeing the speed differences I agree with that!

 

[AlexanderP]

Hi Guys,

Is there a tool that can tell me when a cell have information that is not related to an specific column? For example a name in one cell in a column just made for phone numbers?