locked
One-to-One relationship not working RRS feed

  • Question

  • Hello,

    Using Power Query, I am pulling two tables from http://www.investmentsense.co.uk/accounts-for-pensions/

    I have ended up with two tables with a one-to-one relationship (the key is the Institution column) as shown in the Excel 2013 Power Pivot model.

    I then want to show a simple Pivot with three columns:

    1) Institution
    2) Interest rate (from table 1)
    3) Interest rate (from table 2)

    But, for the 3rd column, in my PivotTable I am getting every rate for every institution! The one-to-one relationship is behaving like a one-to-many.

    I have recreated the same Pivot Table in Power BI Desktop and that is working correctly!

    Thanks in advance!

    Excel Data Model

    Incorrect Pivot Table - behaves like a one-to-many:

    Data Model in Power BI Desk

    Resulting in the expected Pivot Table:


    • Edited by amir tohidi Tuesday, May 17, 2016 1:52 PM Added missing image
    Tuesday, May 17, 2016 1:50 PM

Answers

  • Based on our experience, clearly that is not true.

     It absolutely is using the same technology under the hood. The problem is that you are comparing Excel 2013 which is a 3 year old technology (effectively v2 of the technology) with Power BI which is effectively running the third generation of the technology and is being updated monthly. Everything that works in Excel will work in Power BI, but Power BI has a few newer features that have not been added to Excel yet because of Excel's slower release cycle.

    1:1 relationships are one of those newer features. I believe that the reason they were not added earlier is that they don't really provide any additional analytic capabilities over just having all the columns in one table and with a tool like Power Query that is simple to do for end users without needing IT support.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by amir tohidi Thursday, May 19, 2016 2:20 PM
    Thursday, May 19, 2016 12:56 PM

All replies

  • Easiest solution is probably to do a merge join in Power Query (maybe a left join looking at your data) so that that the data is actually in one table.

    http://darren.gosbell.com - please mark correct answers

    Wednesday, May 18, 2016 5:23 AM
  • Thanks Darren, but that is not an option. We are introducing Power Pivot etc so that business users rely less and less on IT.

    I am guessing that this is a bug with Excel's Power BI which is very disappointing given how simple the data model is.

    I hope someone from the Excel / Power Pivot team replies...

    Wednesday, May 18, 2016 8:30 AM
  • I am guessing that this is a bug with Excel's Power BI which is very disappointing given how simple the data model is.

    It's not a bug. Excel 2013 does not have support for 1:1 relationships (and it does not look like Excel 2016 has this either). You either have to get your users to use Power BI Desktop or to adjust their data models. 

    The tabular engine is quite tolerant of bad data models, but at the end of the day nothing beats a good data model.

    I had a business user referred to me a few weeks ago who was struggling with a DAX calculation. I could have written a complex expression for them, but they would never be able to maintain it. But when I pointed out that moving a column from one table to another would make the calculation trivial the user was able to jump into Power Query and fix this themselves in a few minutes.


    http://darren.gosbell.com - please mark correct answers

    Wednesday, May 18, 2016 11:59 PM
  • Thanks Darren.

    The problem is that our business user immediately lost confidence when we hit this issue on such a simple data model. It didn't help that it worked perfectly in Power BI Desktop - she expected the same behaviour from Excel because all the books etc keep saying Excel and PBD use the same technology under the hood. Based on our experience, clearly that is not true.

    Thursday, May 19, 2016 11:42 AM
  • Based on our experience, clearly that is not true.

     It absolutely is using the same technology under the hood. The problem is that you are comparing Excel 2013 which is a 3 year old technology (effectively v2 of the technology) with Power BI which is effectively running the third generation of the technology and is being updated monthly. Everything that works in Excel will work in Power BI, but Power BI has a few newer features that have not been added to Excel yet because of Excel's slower release cycle.

    1:1 relationships are one of those newer features. I believe that the reason they were not added earlier is that they don't really provide any additional analytic capabilities over just having all the columns in one table and with a tool like Power Query that is simple to do for end users without needing IT support.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by amir tohidi Thursday, May 19, 2016 2:20 PM
    Thursday, May 19, 2016 12:56 PM
  • Thanks for the clarification Darren.

    Because of Power BI's monthly cycle, we have decided to use PBD as opposed to Excel. The Office 2/3 year release cycle is too long to wait for new features.

    Thursday, May 19, 2016 2:22 PM