Pivot table or best summary table help

I would use power query/Get and Transform. Bring all your data including column Z to PQ.
Delete all rows having X in Column Z.

Unpivot your data to put it in a normalized data set. Close and Load to Excel. Now you can do analysis based upon categories. You can pivot the data where necessary.

I will run a scenario for you, but this is a very powerful piece of Excel. You may want to pick up the Book "M is for (Data) Monkey" by Ken Puls and Miguel Escobar. A great PQ tutorial. Available on Amazon. I will post back in a few minutes.

First thing I did was replace all "-" in your original ws with zero (0). Then I removed the autofilter.
1. I highlighted your table including column Z. Opened Get and Transform and selected from table/range
2. I clicked on the column formerly Z and selected the filter. I unchecked those with an X and the rose were removed
3. I then highlighted the last two columns and right clicked and selected remove columns
4. With the data in PQ, I then moved by dragging the descriptive columns to the left as shown in the final layout.
5. I next highlighted all the data columns and right clicked and selected Unpivot columns.
6. On the Home tab, I selected close and load and the data was brought back to Excel as seen on the attached.
7. Your data is now in a form that is normalized that you can do analysis by category. Using the built in Filter will allow you to compare

For average and median, I would use the AverageIf function. Look at Techonthenet.com for syntax for the function or https://support.office.com/en-us/art...8-f7c5c3001690

As to additional columns, you would use the same steps. I would urge you to look at getting the book I described as there are more advanced functions, you may find helpful.

Just as an aside, there is another very recently published book by Gil Raviv that is highly recommended by me called Collect, Combine, and Transform Data Using Power Query in Excel and Power BI (Business Skills). Well worth the investment.

Thanks Ali, just ordered it for myself.

Alan - very wise choice. Gil has a blog as well - you might want to bookmark it.

If your dataset layout is complete, then any changes within the prescribed data set will update when you click on the refresh button in Excel. Sometimes you have to refresh twice. Once to update the excel table and the second to update the PQ data that is transported back to Excel.

To be honest, I don't know. What happens when you test?

It depends on what you are going to do with the errors when you get to PQ. If you are going to delete those lines then you do nothing and filter the rows out in PQ. If you need to keep them, then you will need to change them to a numerical value as they will conflict in PQ and you will not be able to get your results. Probably best to make them zero so they will be recognized as a numerical value.

When I tried to work with your sample and not change the "-" to zero, PQ would not evaluate because you had a non numerical value in the column which was supposed to be numerical and it couldn't handle it.

Is there a querry in PQ that will create a summary table

Thinking that you may need to do this in Excel after completing the PQ. PQ is all about data manipulation.

As to the "multi cell arrays", this is something I know nothing about. Sorry.

no attachment

Originally Posted by dianaCatz

Hi Alan, Also - if I have hidden columns, do those stay hidden when it goes to PQ (I want it to stay hidden and not work with those columns but cant remove them bc other cells drive formulas off of them).
Thanks

Perhaps this explanation might help?

When you upload data into the PQ editor, it does not make any changes at all to the underlying data. Whatever you do to the data in PQ will be reflected in the resulting table that you load back into the workbook, but the original underlying table will remain unaltered. To update the table created by PQ when you change data in the underlying table, you have to refresh the query.

In order to create a summary table, you can open your query and, using the queries panel on the left of the PQ window (often minimised by default), right click the query and chose Reference. This makes a copy of the query which you can further manipulate into a summary table. When you close and load again, you will now have TWO PQ tables in your workbook. Each time you refresh the queries, they weill draw data from the one underlying table.

This is all in Gil's book, so if you've bought that, you'll be able to find where he explains these processes.

You are welcome. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.