How to create pivot Table from multiple tables

Question

I am trying to generate a pivot table from this Schema:

Excel Scheme

Basically the only way I can think off is formatting it as 3 different tables, however that doesn't seem to work as cannot filter by category and year as seems to think that every column 2018/2019 is different so the resulting pivot table doesn't combine them, also the Category being a Row heather doesnt work either. Any suggestions on creating a pivot table from the above that can filter things like lets say Total Sales in 2019 (across Categories) or Gloves sold in 2018.

Thanks

Here is a Microsoft support article with instructions to create a pivot table from multiple data tables: support.office.com/en-us/article/… — Sean

teylyn teylyn · Accepted Answer · 2020-01-27T19:43:30

If this data is a pivot table, you have access to the source data, so you can change the pivot table to show what you need.

If that data is a dump with the layout as shown, then in order to use that data as a source for a pivot table, you first need to unpivot/normalize it, so it has the columns Category, Item description, Year and Value.

This can be done by loading the data into Power Query and clicking a few buttons. There are lots of tutorials out there for unpivoting in Power Query and how to transform data with it.

The green table has been generated with a few clicks in Power Query. The recorded code is below. (Note that none of this code was typed. All I did was click icons in a ribbon.)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"Category:") then [Column1] else null),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","Category: ","",Replacer.ReplaceText,{"Custom"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Column1", "Item"}, {"Custom", "Category"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Item", "Category", "Column2", "Column3"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Column2", "2018"}, {"Column3", "2019"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each ([Item] <> "Category: Accessories" and [Item] <> "Category: Pants" and [Item] <> "Category: Shirt" and [Item] <> "Total")),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Item", "Category"}, "Attribute", "Value"),
    #"Renamed Columns2" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Year"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns2",{"Category", "Item", "Year", "Value"})
in
    #"Reordered Columns1"

How to create pivot Table from multiple tables

1 Answers