Nov 20 2020 07:18 AM
Hello Everyone,
I have a customer list for my business.
This list includes A1: Name, B1: Address, C1:Phone Number etc... (This is first part of my table)
Then the values start: K1: Number of Unit; L1: Sales, L1:Annual Consumption etc... (This is the second part of my same table)
I want to show the 1. part on the top and the 2. part on the pivot table at the same time.
How can I bring them together in one pivot table sheet?
Please helps..??
I am using Microsoft 2019 (window operating system)
Nov 20 2020 07:27 AM
You may Power Query your source data and return by it as many tables as needed. Plus PivotTable from data model if necessary, but I guess all tables could be generated by PQ only.
Nov 20 2020 07:44 AM
Nov 20 2020 08:45 AM
Does you version of Excel supports Power Query and do you have at least some experience with it?
Nov 20 2020 09:23 AM
Nov 20 2020 09:42 AM
If these two tables are for one customer at a time, you need to define somewhere which customer to select. That could be a named cell or like which you may query and use as parameter.
Query source data, do some initial transformations in this query if necessary and filter on customer taken from previous query. Keep this query as connection and load to data model.
Create one more query as reference on above, keep only customer info columns, transpose it and return to Excel as first table in sheet.
Make another reference and transform data for the second table or create from data model PivotTable in Excel sheet. What's better and more suitable depends on concrete data.
Something lie this.