98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Many to Many using bridge table in Powerpivot | Excel forum
Many to Many using bridge table in Powerpivot | Excel forum
Resolved · High Priority · Version 2010
Thu 5 Apr 2018, 18:27Delegate Tsudoi said...
Tsudoi has attended:
Excel PowerPivot course
Excel VBA Introduction course
Many to Many using bridge table in Powerpivot
Hi Jens,
Please can you have a look at the example I've sent you?
I have two tables (will be more) that I want to join by bridge table (by Date). These two tables contain same data only dates are different. I've tried to make pivot table but not working.
In essence, I want to be able to add a table month on month onto data model = pivot table using the bridge table.
Please can you help?
Thanks,
Fri 6 Apr 2018, 09:04Trainer Jens said...
RE: Many to Many using bridge table in Powerpivot
Hi Tsudoi,
Thank you for the forum question.
You can not use the date table as bridge table if you want to group the data on "Division". You will need to create a "Division" table where the divisions are unique and then relate your Month tables to the division table.
It is not the best solution to work with bridge tables in your situation. The data you work with is not related data. You will have to create a bridge table for each grouping you need in the report.
If I was you I would append the tables into one. This will make the data model much more dynamic, flexible, and less time consuming to update.
Best solution is to use Power Query to transform your sheets to one table in the data model and if you use the From Folder option (see video on Youtube) you can each month just drop the new file in the folder and your report will update.
Link to Youtube:
https://www.youtube.com/watch?v=a7E29H5ZUmE
You can also write a SQL Union query to transform all worksheets to one table in your data model.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 6 Apr 2018, 13:54Delegate Tsudoi said...
RE: Many to Many using bridge table in Powerpivot
Thanks Jens.
I've tried PowerQuery and appended all the files into one Data Model (connection only so the file won't be as heavy) and made pivot from that. The only problem is, when I filter criteria, I see all the headers are included. I've selected Use First Row as Headers under Table tab but I still see it. I guess that was applied to only first file and all others files picked up the headings when appended and it would happen again when I add a file into the same file and refresh going forward?
How can I get rid of that please?
Thanks,
Fri 6 Apr 2018, 14:34Trainer Jens said...
RE: Many to Many using bridge table in Powerpivot
Hi Tsudoi,
I cannot tell you what went wrong. Of course we shouldn't get the headings in the data model more than ones.
Please make sure the source data are in dynamic tables as I told you on the course.
If you have the data in a dynamic range you will not need to promote headings.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 6 Apr 2018, 15:38Delegate Tsudoi said...
RE: Many to Many using bridge table in Powerpivot
Thanks for your reply.
I really don't know how to result the issue.
Please can you have a look at the sample I've sent you and see what's gone wrong and how to resolve it...?
Thanks,
Fri 6 Apr 2018, 15:55Trainer Jens said...
RE: Many to Many using bridge table in Powerpivot
Hi Tsudoi,
I am sorry but it is outside the scope of our forum to amend and find errors in our clients workbooks.
The guide lines for our forum is also that all contact should be through the forum and emails should not be sent to trainers email addresses.
We can help you set up a working data model, but you will need to contact Luke Gardner to discuss a consultancy.
Anyway I cannot see how you have created the query without having the source data.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:How to apply the same formatting and data to multiple sheets at the same time in Excel 2010When you want to format more than one sheet in a worksheet exactly the same way, Ctrl-click the tabs of the sheets you want to group together and they will all turn white. While they are grouped, anything you enter in one sheet gets entered into the others. |
