Merging to unpivoted tables and using that in pivot table|Power Query|Excel Forum|My Online Training Hub

Hi all,

I am quite new to pivot queries and have marginally achieved what I want except as the data has grown the time to refresh has increased considerably and in some cases I get the message that there is insufficient memory.

First off let me state that I am currently forced to use Excel 2013 for this work.

The spreadsheet I am am working on has 2 sheets each with a matrix of engineering data of sized 155 rows by 124 and 92 columns respectively. For example matrix one maps Functions vs Tests and Matrix 2 maps the same Functions vs Hardware (the mapping variables are not necessarily the same and the source information comes from different areas)

I have used 2 power queries one to unpivot each matrix. And have set these as connection only as there is no need to visualise the unpivoted tables.

I then use a third query to do a merge where I do LeftOuter Join and Expand to keep only the The Hardware type and Hardware Mapping. This is also set as connection only.

This 3rd Query is then used as the source for a pivot chart in a new sheet which enables slicers for Functions and Hardware and Tests to be implemented so users can slice by hardware and find applicable tests or vice versa.

(I attached a sample which should give the idea of what I am trying to do. The small sample works fine...but merging the 15k plus row tables seems to cause issues and slow performance.)

Having read through some of these posts and referenced articles to Chris Webb's Blog, I think that there should be a way to optimise this spreadsheet but have not been able to do so. There are 2 points that I have picked up on but not sure if or how I should implement in my case:

1 - is the use of a Primary key or Table.AddKey() but in my case the fields I am merging on are no longer unique due to the unpivot and also I am doing an expand and not an aggregate. So should I still be having a unique key and if so how would I implement it?

2 - is the use of Table.Buffer(), which I am also not sure if I should apply or to which query? (in my case I am not expecting users to modify the data so I would not need the queries to be rerun after the first time. Though I have the feeling that the 3rd query gets rerun depending on what you do with the pivot chart...for example I tried grouping a field in the pivot and when I tried to ungroup excel did not cope and ran out of memory)

(Note also I have unchecked allow data preview and am not using fast data load as it says excel will remain unresponsive for long periods and it is already unresponsive following a refresh...not really understood how the Fast Data option should help)