If y'all are a regular Microsoft Excel 2016 user, but non quite familiar alongside Power Query Add-in for Excel, as well as then this post service volition introduce as well as help y'all expire started alongside it. Any advanced Excel user faces challenge of analyzing or creating reports alongside PivotTables as well as PivotCharts every mo they lack the expertise inwards this domain as well as oft terminate upward showing static tabular data, or summaries. Though it is slow to purpose this tabular array for quick information entry, it is hard to start information analysis on such tabular array or convert it into PivotTable as well as shape it to encounter your analysis needs.
Look no further, alongside Power Query technology scientific discipline inwards Excel, y'all tin alter this. The novel capability added to Excel 2016 allows y'all to unpivot static tables, ie. access your tables, perform a transformation as well as exercise a PivotTable from the transformed data, inwards Excel 2016.
Unpivot Static Tables inwards Excel 2016
Consider y'all convey an Excel workbook that is dedicated for manual information entry to rails the rank out hours y'all expend energies on a unmarried relevant project.
Analyzing this information tin endure a daunting task. You wish, if y'all could but exercise charts on this data. Fortunately, through the novel Get & Transform department inwards the Data tab of Excel 2016, y'all could consummate the task. To exercise so,
Simply pick out whatsoever prison theatre cellular telephone within an existing tabular array or attain as well as inwards the Get & Transform section, click From Table.
You volition reckon a preview of your information within the Query Editor window. Under this window, y'all tin start transforming your data. Each alter y'all brand is recorded every mo a transformation stride that is saved alongside your workbook. All the steps are kept every mo a sequence as well as tin endure performed every fourth dimension time y'all refresh your data.
In the Query Editor, y'all volition unremarkably demand the help of the Unpivot transformation to alter your tabular array into a format that tin endure used yesteryear PivotTable.
As y'all tin notice inwards the example below, I tin pick out the starting fourth dimension column that includes projection names as well as click Unpivot Other Columns to transform my information from columns of Project Name / 2010 / 2011 / 2012… to the desired column construction of Project / Year / Duration.
And here’s what y'all volition abide by every mo the results inwards the Query Editor:
I tin immediately rename the columns to Project, Year as well as Duration within the Query Editor as well as and then charge the information into a PivotTable or PivotChart.
Source: Office Blogs.
Source: https://www.thewindowsclub.com/
comment 0 Comments
more_vert