Creating new table based on other 3 tables values

[hejszyszky]

Hi, 
My problem is as below.

I have table 1 structured like:
Date , Name , Value 
Table 2:
Date , Name , Value

Table 3 : Calendar (single values)

Table 1 and 2 contains multiple date & name values.

I would like to have a table 4 which contains summarized column value by Date(days) and Names as below
Date(Days), Name, Value(t1), Value(t2)

Could someone relate to this?

Thanks in advance!

[amitchandak]

@hejszyszky ,

New Table =

var _Tab = Union(

Summarize(Table1, Date[Day], Table1[Name], "Value", sum(Table1[Name])) ,

Summarize(Table2, Date[Day], Table2[Name], "Value", sum(Table2[Name]))

)

return

_tab

or

New Table =

var _Tab = Union(

Summarize(Table1, Date[Day], Table1[Name], "Value", sum(Table1[Name])) ,

Summarize(Table2, Date[Day], Table2[Name], "Value", sum(Table2[Name]))

)

return

Summarize(_tab, [Day], [Name], "Value", sumX(_tab,[Value]))

[hejszyszky]

Hi, Thanks for your response @amitchandak !

I forgot to add that the values refer to something different, so union wont work well here 😞 (names are the same tho)

My bad!

[hejszyszky]

For better undestranding:

Table 1:
Date             Name Value1 Value2
1-11-2021    A           3           500

1-11-2021    A           4           50

2-11-2021    A           5           5
2-11-2021    B            6           5000

3-11-2021    A           7          5000

Table2:

Date             Name Value 
1-11-2021    B           3          

1-11-2021    B           5 

1-11-2021    A           4          

2-11-2021    A           5          
2-11-2021    B            6         

3-11-2021    C           7          

Outside of table 1 and 2 i would like to create Table3 as below:

Date           Name Value Value1 Value 2
1-11-2021  A         0           7        550

1-11-2021  B          8         0         0

2-11-2021  A         5          11       5005

2-11-2021  B          6          6        5000

3-11-2021  C          7          0           0

3-11-2021 A          0          7          5000

[amitchandak · Accepted solution]

@hejszyszky , best is append and group by in power query ,as ut can taken column that do not match too

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://docs.microsoft.com/en-us/power-query/group-by

or

New Table =

var _Tab = Union(

Summarize(Table1, Date[Day], Table1[Name], "Value",  0, "Value1", sum(Table1[Value1]) , "Value2", sum(Table1[Valu2])    ) ,

Summarize(Table2, Date[Day], Table2[Name], "Value", sum(Table2[Value]) ,  "Value1",  0 ,"Value2",  0 )

)

return

Summarize(_tab, [Day], [Name], "Value", sumX(_tab,[Value]), "Value1", sumX(_tab,[Value1]) , "Value2", sumX(_tab,[Value2])     )