Avoiding double values using relational tables in power pivot tables

[redalert787]

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, 

[v-kelly-msft]

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:

https://community.powerbi.com/t5/Desktop/Duplicates-returned-with-Table-Distinct-in-M-Query/m-p/6964...

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Jimmy801]

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