Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hejszyszky
Helper II
Helper II

Creating 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!

1 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])     )

 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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]))

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])     )

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.