Hello,
I have a question about relating tables which have many to many relationships. I haven’t finished the Power Query course, so this may be discussed in one of the lectures. To clarify what I’m trying to do, I created 2 examples, one that works and one that doesn’t.
Let’s say I want to calculate the cost to manage my household. I’ve got some raw (direct) data for electricity, refuse, air conditioning and heating. And, let’s say I also know that my electricity gets allocated to heating, cooling and then “all other appliances” in known percentages. I can calculate the total cost for heating from my direct repair bills, plus the allocation of my electricity costs towards my heating bill. That is a 1:many relationship and that works in PowerBI. I modeled that in the tab called ‘Working’. Specifically, I created a calculated column in the Allocated Cost table to determine the allocated cost. I then created a pivot table from the Allocated cost table; I can then get the result of my total costs for things like Heating.
Now for the example that I can’t get to work. Instead of having a raw cost table where I have 1 row for electricity, let’s say I have 4 rows of electricity, one for each quarter in the year. But, let’s also say that each row is labelled the same i.e. Electricity rather than Electricity Q1, Electricity Q2, etc. This example is shown in the tab called “Not Working”. If I try to relate the raw cost table and allocated cost table, I end up having a many: many relationship which I don’t know how to model directly. I know that a work around in MS Access is to add a helper table. The helper table contains a unique list of the cost categories in this case. And, I know the idea is to relate each of the “many” tables to the one helper table. I haven’t done that in PowerBI, but even if I did, I don’t know exactly how to create the calculated formula such that the example still works. Does anyone know how to do this?
I’m certainly open to other examples to manage many:many tables in PowerBI. I hope the examples I provided make sense in a forum posting.
Regards,
Cory
Hi Cory,
Thanks for providing such a clear example and file.
You can do this in Power Query using Merge As New, then use a Right-Outer join so that you bring in all of the Raw Housing Data, and only the Allocations that are required. You can then add a column to do the allocation.
See example file attached.
Mynda
Hi Mynda,
Thank you for your quick reply. Since I signed up, but haven't taken the Power Query course yet, it took me a while to review your steps to understand what you did. But, I see that you created queries for each of the 2 "source" tables and then merged them as you noted above. Then, I saw that you expanded the column that contained the other table and then selected just the columns you wanted. This made the data from the other table appear in all the rows so you perform the simple multiplication. Slick!
And, although I didn't include it in my original requirements, the best of all is that when you have that merged query and then create the pivot to see the summary, you can always double click on the pivot table to see the underlying data (in detail) if you need to.
Thanks again. I look forward to finishing the PowerBI courses.
-Cory
Sorry, I didn't realise you hadn't even started the Power Query course. I'm glad you figured it out.
I'd do the Power Query course first since getting and cleaning your data is the foundation for everything else.
Mynda
