Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-393814%22%20slang%3D%22en-US%22%3ECombine%20Data%20from%20Multiple%20Tables%20to%201%20Pivot%20When%20Having%20Duplicate%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393814%22%20slang%3D%22en-US%22%3E%3CP%3ESee%20attached%20file.%3C%2FP%3E%3CP%3EI%20have%20a%20table%20(Table1)%20with%20data%20of%20number%20of%20resources%20required%20every%20month%20per%20Product%20%2F%20per%20Domain%20%2F%20per%20Team.%20So%20a%20single%20product%20can%20appear%20many%20time%20in%20the%20'product'%26nbsp%3Bcolumn.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreating%20a%20Pivot%20for%20this%20Table1%20is%20easy%20so%20I%20can%20see%20how%20much%20effort%20is%20required%20for%20each%20product%20or%20how%20much%20effort%20invests%20every%20Team%20along%20the%20year.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20many%20products%20%26amp%3B%20many%20department.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDue%20to%20internal%20limitations%2C%20I%20have%20to%20divide%20Table1%20so%20each%20department%20will%20get%20the%20same%20table%20structure%20but%20will%20fill%20in%20different%20required%20effort%20per%20month.%3C%2FP%3E%3CP%3ESo....I%20have%26nbsp%3BTable2%20for%20Software%20and%26nbsp%3BTable3%20for%20Mechanics%20department%20and%20can%20have%20more%20tables%20for%20other%26nbsp%3Bdepartment.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20couldn't%20do%20it%20with%20the%20Pivot%20Data%20Model%20since%20'Product'%20column%20contains%20duplicate%20values.%3C%2FP%3E%3CP%3ESo%20-%20How%20do%20I%20combine%20all%20the%20tables%20to%201%20pivot%20or%20to%201%20table%20and%20from%20there%20creating%20the%20pivot%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-393814%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPivot%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETables%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394403%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20Data%20from%20Multiple%20Tables%20to%201%20Pivot%20When%20Having%20Duplicate%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394403%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-393866%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20Data%20from%20Multiple%20Tables%20to%201%20Pivot%20When%20Having%20Duplicate%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F248491%22%20target%3D%22_blank%22%3E%40yanlt%3C%2FA%3E%26nbsp%3B%2C%20the%20easiest%20way%20to%20combine%20is%20by%20Power%20Query.%20Script%20like%20this%3C%2FP%3E%0A%3CPRE%3Elet%0A%20%20%20%20Source1%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22General%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20Source2%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Software%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20Source3%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Mechanics%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20AppendedTables%20%3D%20Table.Combine(%7BSource1%2C%20Source2%2C%20Source3%7D)%2C%0A%20%20%20%20ToNumber%20%3D%20Table.TransformColumnTypes(AppendedTables%2C%7B%0A%20%20%20%20%20%20%20%20%7B%22Jan-19%22%2C%20type%20number%7D%2C%0A%20%20%20%20%20%20%20%20%7B%22Feb-19%22%2C%20type%20number%7D%2C%0A%20%20%20%20%20%20%20%20%7B%22Mar-19%22%2C%20type%20number%7D%2C%0A%20%20%20%20%20%20%20%20%7B%22Apr-19%22%2C%20type%20number%7D%2C%0A%20%20%20%20%20%20%20%20%7B%22May-19%22%2C%20type%20number%7D%7D)%0Ain%0A%20%20%20%20ToNumber%3C%2FPRE%3E%0A%3CP%3Eis%20generated%20from%20User%20Interface%2C%20load%20that%20query%20as%20connection%20adding%20data%20to%20data%20model%2C%20and%20build%20PivotTable%20from%20data%20model.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
yanlt
Occasional Contributor

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.

 

Creating a Pivot for this Table1 is easy so I can see how much effort is required for each product or how much effort invests every Team along the year.

 

I have many products & many department.

 

Due to internal limitations, I have to divide Table1 so each department will get the same table structure but will fill in different required effort per month.

So....I have Table2 for Software and Table3 for Mechanics department and can have more tables for other department.

 

I couldn't do it with the Pivot Data Model since 'Product' column contains duplicate values.

So - How do I combine all the tables to 1 pivot or to 1 table and from there creating the pivot?

 

 

2 Replies
Highlighted

@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.

 

Highlighted
Related Conversations
Duplicate Favorites/Bookmarks
HotCakeX in Discussions on
0 Replies
Add Feature Duplicate Video
GoceR in Microsoft Stream Forum on
2 Replies
History and Data
rosenbloomsnka in Microsoft To Do on
0 Replies