Forum Discussion

yanlt's avatar
yanlt
Copper Contributor
Apr 03, 2019

Combine Data from Multiple Tables to 1 Pivot When Having Duplicate Values

See attached file. I have a table (Table1) with data of number of resources required every month per Product / per Domain / per Team. So a single product can appear many time in the 'product' column...
excel
Pivot
Tables
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 03, 2019

yanlt , the easiest way to combine is by Power Query. Script like this

let
    Source1 = Excel.CurrentWorkbook(){[Name="General"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Software"]}[Content],
    Source3 = Excel.CurrentWorkbook(){[Name="Mechanics"]}[Content],
    AppendedTables = Table.Combine({Source1, Source2, Source3}),
    ToNumber = Table.TransformColumnTypes(AppendedTables,{
        {"Jan-19", type number},
        {"Feb-19", type number},
        {"Mar-19", type number},
        {"Apr-19", type number},
        {"May-19", type number}})
in
    ToNumber

is generated from User Interface, load that query as connection adding data to data model, and build PivotTable from data model.

 

  • yanlt's avatar
    yanlt
    Copper Contributor
    Apr 04, 2019

    Thank you SergeiBaklan .

     

Resources