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

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

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies