Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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]) )
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]))
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!
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
@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]) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |