I have tried using Pivot Tables many times and have been satisfied the result exactly once.
I find the GUI for setting them up very cumbersome and confusing.
I am wondering if anyone can point me to good resources for better understanding pivot tables and their functionality. Thank you.
You need to investigate Power Query, which is bundled with Excel 2016. Lots of tutorials online and much easier to use, in my view. I agree with you - I find pivot tables cumbersome.
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.
I find them useful & simple to use.
Just show some examples and what you would like to accomplish.
Last edited by snb; 02-03-2018 at 12:14 PM.
Erm...Originally Posted by AliGW
Power Query and Pivot Tables do completely different things...
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
You are going to need to explain that, Olly.
Power Query is a tool for blending and shaping data from a variety of sources (e.g. text files, database tables, online sources). It's used to normalise data, so that it may be analysed effectively.
Pivot Tables are used to analyse and present information, based on normalised data sources (and Power Pivot is a more powerful version of Pivot Tables, that allow even greater control of the data modelling).
Use Power Query to GET data.
Use Pivot Tables to CONSUME and ANALYSE data.
PowerQuery, is data gathering tool, specializing in data transformation.
Typically, PowerQuery's role is to clean up data "before" it gets loaded to data model.
PivotTable and/or PowerPivot's role is to model the data (relationships) and define calculations.
There are some overlaps in functionality, but for best performance, it is best to leave out data transformation and cleanup out of PivotTable and PowerPivot.
See link for brief overview.
https://support.office.com/en-us/art...f-b21d69990bc0
I understand what each of the tools is designed to do, but the reality is that I can do all the pivoting I need to do with PQ. In other words, I can do everything I need to do (normalisation and analysis) with PQ and Excel without having to use pivot tables at all. And I don't mean by using lots of complicated array formulae! You can easily create tables that look just like pivot tables using PQ that do the same grouping and analysis of data.
@TFiske
I use PT and when I started, here was one of the resources I leaned upon.
http://www.datapigtechnologies.com/ExcelMain.htm
Scroll down to the section on PT
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
True, but that limits what you can do with result.You can easily create tables that look just like pivot tables using PQ that do the same grouping and analysis of data.
Unlike pivot table, you can't use slicer and other tools to slice and dice data in different manner quickly.
Pivot table allows for quick expand/collapse of grouping details, time intelligence, without change to underlying model/data.
Linking multiple tables via slicer to present same segment but with different summary etc.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks