Pivot table for same types of values in different columns

user_16521653 0
Viewed 318

I have this dataset where the same person could be in two different columns and so his/her sales records. Is there any way to see their aggregated sales record by person and country using pivot table?

MS Excel 2016

Dataset sample and the way Pivot table should be

pivot-table excel
1 Answers

I think you'd better use Data>Get&Transform or Data>From Table/Range (I am not sure name in excel 2016, I am using 365). This is to bring your table to Power Query and transform it.

The key thing here is to Transform>Unpivot Other Columns (out of Country and City columns), values: Person1, Person2, Person1_sales, Person2_sales to 1 column. Next remove "1" and "2" to make that column includes only 2 values: Person and Person_sales. Then Transform>Pivot that column becomes 2 columns: Person and Person_sales:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Country", "City"}, "Attribute", "Value"),
    #"Added addedName_temp" = Table.AddColumn(#"Unpivoted Other Columns", "addedName_temp", each [Country]&Text.Middle([Attribute],6,1)),
    #"Replace '1'" = Table.ReplaceValue(#"Added addedName_temp","1","",Replacer.ReplaceText,{"Attribute"}),
    #"Replace '2'" = Table.ReplaceValue(#"Replace '1'","2","",Replacer.ReplaceText,{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Replace '2'", List.Distinct(#"Replace '2'"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"addedName_temp"})
in
    #"Removed Columns"

Table after transformed looks like: enter image description here

Here your result in excel pivot: enter image description here

You can still use this query in case you have many pairs of Person and Person_sales. Have fun.

user_119167590
Related