Forum Discussion
Helper IICreating new table based on other 3 tables values
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!
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]) )
4 Replies
- amitchandak
Super User
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]))
hejszyszkyHi, 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 5001-11-2021 A 4 50
2-11-2021 A 5 5
2-11-2021 B 6 50003-11-2021 A 7 5000
Table2:
Date Name Value
1-11-2021 B 31-11-2021 B 5
1-11-2021 A 4
2-11-2021 A 5
2-11-2021 B 63-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 5501-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