Forum Discussion

Flipper's avatar
Flipper
Frequent Visitor
4 years ago

Using Excel Connector with PBI Datamodel

Hi Guys   I'm working with a client who basically wants to create a set of data that can be consumed through Excel, so I've recommened that they can use PBI to build out a datamodel and then use th...
Need Help
Tips and Tricks
amitchandak's avatar
amitchandak
Super User
4 years ago

Flipper ,Based on what I got.

In case table on the one side have less values say have 5 category and fact(many side table) has 6.

You will get one blank row with all measures created from fact when they are used with category.

 

By default is it right join. +0 can force left also.

 

In case you want to remove the blank of one side when value is missing ;you need to use a visual or page level filter for that

 

  • Flipper's avatar
    Flipper
    Frequent Visitor
    4 years ago

    Hi amitchandak 

     

    Thanks for replying. So probably helps to have an example. I've got two tables, lets call them Parent and Child.

     

    Parent has:

     

    ParentID
    Parent1
    Parent2

     

    Child has

     

    ChildIDParentIDfk
    Child1Parent1
    Child2Parent1
    Child3Parent2

     

    If I bring them together, I'd expect to see:

     

    ParentIDChildIDParentIDfk
    Parent1Child1Parent1
    Parent1Child2Parent1
    Parent2Child3Parent2

     

    However, what I believe I am seeing is (In excel - NOT in PBI, works as above in PBI):

     

    ParentIDChildIDParentIDfk
    Parent1Child1Parent1
    Parent1Child2Parent1
    Parent1Child3Parent2
    Parent2Child1Parent1
    Parent2Child2Parent1
    Parent2Child3Parent2

     

    The above feels wrong, but here is the evidence....

     

    Weird eh?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Flipper,

      It seems like these records do not map correctly in the pivot table, have you built the relationship in excel data tables? If not, you can refer to the following link to build the relationship to help records mapping.

      Create a relationship between tables in Excel (microsoft.com)

      Regards,

      Xiaoxin Sheng

      • Flipper's avatar
        Flipper
        Frequent Visitor
        4 years ago

        Hi Xiaoxin

         

        Thanks for responding, but unfortunately I don't think the issue I see is to do with the relationships in excel 😞 That link talks about maintaining the relationships in Excel - this issue is to do with excel connecting to a PBI model on the PBI service.

         

        The above scenario I gave was completely modelled in PBI - the data was created in PBI using the "Enter Data" option and the relationship between the tables is modelled in PBI. This is the model - its about as simple as it can be:

         

         

        This is then the output (In PBI) if I put everything from the two tables into a table viz:

         

        If I then publish to the PBI service and then connect to the same model using the Excel PBI connector, I see the following:

         

         

        It doesn't make sense to me - excel is just the front end here, the relationship is correctly modelled in PBI and is proved to work in PBI, but fails when displayed using excel. Its as if the PowerPivot can't recognise the PBI model?

         

        You can try it yourself if you want (I'm sorry, not sure how you attach an example!!) - all you need to do is lift the tables from the above and build the model in PBI - then publish it and connect using excel.

         

        Thanks

         

        Nick