Composing Tables from many-to-many (denormalization)

[raymond]

Hi Community,

I want to compose/ merge tables and combine their values. I am looking for a method that uses calculated columns (dax) or power query. 

There is a simpliefied scenario: 

- Table A contains Source Data with Cost

- Table B contains User Date based upon the source Data

- A third Table A+B should be created. Look at the image example.

Table A:

Table A

Table B:

Table B

Result Table A+B

Result Table A+B

[Ashish_Mathur]

Hi,

This calculated column formula in Table A will work

=CALCULATE(SUM('Table B'[User]),FILTER('Table B','Table B'[Date]=EARLIER('Table A'[Date])&&'Table B'[Source]=EARLIER('Table A'[Source])))

Hope this helps.

[Anonymous · Accepted solution]

Hi @raymond -

In Power Query, you could do the following:

1. Group Table B by Date and Site, with SUM of User.
2. In Table A, Merge Table B, joining on Date and Site.
3. Expand columns to add User.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[raymond]

hi @Anonymous , thank you. That was easy. Say do you know a way to do it with a calculated column too?

[Anonymous]

@raymond - There are ways to do this, but you wouldn't want to bring a table into Power BI unless you need it there for another purpose.

One way you could do this is:

1. Create a Custom Column in Power Query on each table - concatenate the date and source.

2. Create a Relationship between the tables in Power BI - use the concatenated column for the join.

3. Create a Calculated Column like this:

Sum Of User = 
SUMX(
    RELATEDTABLE('OtherTable'),
    'OtherTable'[User]
)