Data consolidation within a table

[Yecart77]

Hi everyone,

A copy of my sample filed stored in One Drive.

Created sample in 365 but document will be in 2016.

Existing data in Rows 1 to 4 have exact values in Columns A to D but a different value in E.

The outcome I am looking for is like in Rows 10-11 where the values in Columns A-D are maintained but Column E has been summed.

The final presentation basically just needs to be a clean, flat (maybe one dimensional is a good word to use) table as opposed to a Pivot Table format.

I have had a go with the Data / Consolidate feature without success.

Any hints or tips much appreciated.

 

[alansidman]

Solved with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Year ", "Month ", "Item", "SKU"}, {{"Total", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

 

[Yecart77]

Thank you so much @alansidman

I'm very much a novice Power query, could I trouble you to show me where to enter the formula once I am in there?

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Yecart77]

Thank you so much @alansidman. I'm up and running.