Hi,
I have two worksheets.I want a single Pivot table to be created by combining this two worksheets.The label matches between these two worksheets partially.I am working in office 2013/16.Can you help me in this regard.
Sample data workbook is enclosed for your kind reference.
If it was me I would be making sure that what you use as the primary key in each set of data matched exactly before trying to merge them.
I have not done anything as such that.Can you help me in the workbook as provided above.
Re: Pivot table from two different worksheet whose labels partially match
is that what you want?
add more fields if necessary
done with PowerQuery (Get&Transform) and PivotTable
Hi,
can you give me the screenshot in the excel itself so that I can know how you have performed.
open file with Ex2016, go to Data - Show Queries -dbl click on Append1 and you'll see all steps on the right side
I think I could not get the steps.I will be very much more thankful if you could elaborate in detail stepwise (if possible screenshot)as how you have done so that I can do in my original workbook which has a large data.
with Ex2016 (for Ex2013 you'll need PowerQuery add-in)Originally Posted by paradise2sr
I hope I did not miss anything
- select first table
- Data - From Table (it will add table to Workbook Queries)
- in PowerQuery window - Close & Load To - Only Create Connection
- the same with the second table
- Data - Show Queries
- Right click on the first table and select Append
- in new window first table will be choosen
- choose second table
- OK
- Close & Load To
- Only Create Connection
- Insert - Pivot Table
- Use External Data Source
- Choose connection
- Select Query-Append1
- OK
btw. you didn't answer for my question - is that what you want? (result)
Last edited by sandy666; 03-09-2018 at 10:31 AM.
Thanks for your kind reply.I face few problems:
1.Close and load only to create connection was in disable form for 1st table.
2.Query Append 1 created at the same time Query Append 1(2)also created.
3.Date was shown in pivot table as Years, Quarter,Month in 3columns instead of Single date as dd/mm/yy format.
Can you help me how to resolve these issues.
Actually I am making two pivot table for reporting from two worksheets.By using one Pivot Table,it would be easy for analysis as both are interrelated.
Last edited by paradise2sr; 03-09-2018 at 12:01 PM.
ad3.
Ungroup
ad.1&2 you need to wait a few...
ad3 I couldn't get into it.
Ok download this one and unzip: FILE
post file with the problem
Sorry for the delay.I was out of the city.
Ok I have tried your post #10 of ungrouping & it worked and query raised in Post #9 point no.1 & 3 has been resolved.Still point no.2 i.e Append1(2) is also created simultaneously.However the results are fine.
In addition to it,I have changed heading of my table 1 but he same could not be reflected in pivot table.Is there some restriction.Kindly let me know.
I could not be able to post in your desired location,since the file is too big 7MB and confidential.Hope you understand.
After load data from sheet to PQ you cannot change header(s) in source table. You can do that in Query Editor.
Plan your work.
or
if you really want to change headers in source table, make change to the same name in appropriate column in PQ table with every step.
If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.
Thanx for your kind reply.If there is also alternative method which is quick without creating any helper sheet,pls also let me know.
I don't understand too much what you mean, sorry
I mean to say the same result to be achieved by any other way.
Mybe there is but I do not want to make life difficult
maybe someone else will give another solution
have a nice day
Ok thanx for your kind support.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks