Apr 03 2019 06:03 AM
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?
Apr 03 2019 07:33 AM
@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.
Apr 03 2019 11:47 PM
Thank you @Sergei Baklan .