Hi everyone,
Is it possible to compare Pivot tables with each other? This is for the purposes of comparing construction prices from different contractors. For level 1, in the attached Excel, not all contractors have priced each instance of Level 1 entry, and this is common with construction pricing.
Any advice on how to do this would be appreciated
Capture.PNG
You can do this easily with PowerQuery. You just have to name the tables first.
Have a look at the attached - I can explain how it's done if the suggestion suits you.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Ali, thank you for this. Yes, the suggestion looks good, please can you explain the method.
Yes please. I'd just like to add, that the grand total column to the left isn't really necessary, as the Pivot is trying to analyse the different contractors, and there would never be a situation where they are all instructed.
Attachment 741788
Here's what I did:
1. Name each of the three tables using Formulas | Name Manager.
2. Go to Data | Get Data | From Other Sources | Blank Query.
3. Use the following M Code, starting with Excel.CurrentWorkbook():
4. Close & Load To ... | PivotTable Report.
Please Login or Register to view this content.
5. Set up the pivot table fields as required.
If you look at my sample file and go to Data | Queries & Connections, you will be able to launch the query I created (called Pivot) to see the above.
You can configure the pivot table to suit yourself, of course.
Your attachment is invalid, so I don't know what you are trying to show me.
Last edited by AliGW; 07-27-2021 at 01:49 AM.
Thank you. This has worked very well. Is there any of excluding tables from being included in this query? I.e. if I had another table in the workbook, which had nothing to do with the analysis of the contractor prices?
Yes, you can.
In the query, at the first step (Source), simply filter the column named Name.
If the tables you wish to exclude are all tables with default names (i.e. they start with Table), then you can use the Text Filters > Does not begin with ... option in the filter dialog to exclude any table that you have not named yourself.
Alternatively, name the tables you want to include with a specific prefix (e.g. C_, indicating contractor), and then use the Begins with ... option.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Last edited by AliGW; 07-27-2021 at 07:40 AM. Reason: Wrong way round ... :D
Thank you Ali
You're welcome. It always gives me great pleasure to open the Pandora's Box that is PQ for another forum member.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks