Hi, I am using Excel power pivot option to create linking pivot tables. I want to establish a many-to-many relationship between the 3 tables. But the problem is that all of these tables contain duplicated Id, Record id, and date columns. For better understanding, I am sharing a sample Excel file. I will be grateful if anyone suggests me a better way to create a relationship correctly among these tables. Thanks.
Last edited by Hasnat124; 07-25-2023 at 11:31 PM. Reason: Solved
I am not sure what you want an an outcome, however I hope that the following will help.
1. Produce tables of distinct values of Id, Record id, and date
2. Produce relationships between each of the new tables and the three original tables
3. In the pivot table use the fields produced from the three new tables in the Rows area
I put Sum of Total cost (TK) Sum of Total price (Tk) Sum of Fertilizer price (Tk) in the Values area just for illustration that this will allow relationships between the three tables.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Dear JeteMc
Thank you for your response. I am newly working with power pivot. I am searching for something exactly like this. From this above-mentioned data set, I want to create a summary pivot table with some basic calculations by joining sub-tables. As you showed in the example Excel file, I think I have to create unique ids, dates, and record IDs to make relationships among these tables. Your way of connecting tables seems to be easy for me and I will try it in my original dataset. Thanks again.
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks