Forum Discussion
Helper IIAvoiding double values using relational tables in power pivot tables
Hello,
I'm trying to use power query and power pivot to build relationtional tables and make dashboards in excel. I've done the step of making unique tables to connect the dimensions so there are no many to many relationships. However, if I want to use one of those dimensions and bring it in as a row in a pivot table, then bring in the value from 2 of the relational tables, I have to bring them in as two seperate values and it creates two columns.
For example, if I want to look at sales by week for both retail stores and ecommerce, that each have their own database/table in power query. I am connecting the tables using week in the rows (dimensions) but for sales I have to bring in the sales from both the retail and ecommerce tables and that creates two rows in the values column where I only want one.
Any thoughts? Am I missing something easy?
Thanks,
2 Replies
Jimmy801Community Champion
Hello redalert787
probably you have to write some custom measures or create some more relationships. But I still don't have any idea how your datamodel looks like and what your expected output is. If you could post some screenshots of your datamodel, maybe we are able to help, or to share some excel file with dummies, where we could work on
BR
Jimmy
v-kelly-msftCommunity Support
Hi redalert787 ,
Try to use Table.Distinct function to realize it.
Below is the reference:
https://docs.microsoft.com/en-us/powerquery-m/table-distinct
Below thread can be taken for instance:
Best Regards,
KellyDid I answer your question? Mark my post as a solution!