Reformatting Summary Data for Table

Hello,

I have data that was manually grouped into summary tables that I would like to use as PivotData to graph/chart different scenarios. There are several summary tables of data with several columns labeled as 2015, 2016, 2017, 2018, 2019....I'd like to transform this data into a data list that a PivotTable can make good use. To do this, I need to convert the header columns into data for each specific entry row in teh form of a year column. I think convert these manual tables into actual excel tables and using PowerQuery is the way to go. I want to avoid doing this the hard way by having to copy and paste each row and adding the year data for each row. Figured there had to be a way to almost "reverse pivot" it into the format that I need it.

My ultimate goal is to use the bottom table to analyze a combination of scenarios as a sensitivity study.

Thanks!

I don't know what are you tryin' to achieve but maybe this one

Hey Sandy666! Thanks for the reponse. More or less I am trying to create a column as "Year" Listing the year and coresponding data for that year on teh column in each row. So instead of columns 2015, 2016, 12017.... there will just be one column year with the coresponding data for each row. This will make it easier as data by year into a pivot.

Sorry, new to PowerQuery. But yeah, that is what I want to do with the data!

Is there a quick simple trick to transforming that data into that format or is it a manual approach?

In PQ.

On a related note, I am using Excel 2016. The Power Query protion of the ribbon does not display nor can I select it from the Com Add Ins.

1. change excel version in your profile to proper version
2. PQ should be in the ribbon of Excel 2016 by default, if not you need to look at Excel add-ins or COM add-ins but you'll find there PowerPivot only (or Power View)
3.

• load (as you can see) table into PowerQuery Editor
• remove unnecessary columns (blank)
• add Conditional Column where Column Market contain word Total - show 1
• sort descending this column
• remove first 4 rows
• select all Year columns
• unpivot selected columns

that's all

EDIT:
I forgot to add info: PowerQuery in Excel 2016 is known as Get&Transform - Data Tab - section: Get&Transform

gt.jpg

Read me Try to avoid joining to my Black List by doing this below If that takes care of your original question, & to say Thanks and for better Motivation, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you) then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED. If you did it already - ignore it. Thank you.

Thanks Sandy. I messed around and did something a bit different.

- Loaded queries
- Appended Queries to combine the the different tables
- Open query editor
- Selected the year columns
- Unprivoted selected columns
Renammed Attribute columns to Year

I recently got upgrades to 2016 and other sites had said that. So basically to access any query, you have to go through "show queries" and right click to edit, correct?

I don't know why you did what you did but if it works for you that's ok

and right: show queries and edit or simply dbl click on any table there and it will open PQ Editor

here you need edit your profile and change Excel version to 2016 Pro Plus will be easier for us and for you

---
btw. see highlighted section in post#8

I realized what I wanted to do and how to do it. Basically, I imported my tables into Power Query. I selected the rows that I wanted to return to data tables and not flattened tables. Basically I needed to "unpivot" the tables. Well lo and behold, Power Query has an "unpivot selected columns" function. Having selected the columns I wanted to unpivot and then clcking this function under transform did everything I need in only a few clicks.

GAMECHANGER!

Also the split cells by delimiters is another impressive tool for cleaning data.

Yeah. And you told me to do exactly what I was trying to do. Thank you so much!

On another note. Do you know how to get PowerPivot to calculate a a standard deviation for each row (rows are customers, products, and shipment container) across columns (in this case months 1...12) with values being the pounds of product. I want to get a standard deviation calculated for each row across the month columns and the standard deviation needs to be done on the pivot results, not the source data. I'd share a table, but the problem is, its full of sensitive information and would take a long time to dummy. (unless you have a short cut way to dummy a table.)